connect with different NT-User to MSSQL

Question

_{Monday, December 13, 2010 11:19 AM}

Hi forum,

I'm trying to connect from a powershell script to a mssql database. Everything works fine when using SQL User accounts. When using NT-User accounts the authentication fails. For NT-User I'm setting "Integrated Security=true" in the connection string. This seems to ignore the userid and pwd and use the local user account (system-user in my case) and is not correct. When setting "Integrated Security=false" the authentication fails because is seems that the script is assuming a SQL User account.

so how the would it be possible to connect to the database using an NT User account? Thanks for your help in advance!

here is a example function:

function test_ima_db {
    $SQLCommand="SELECT * FROM INFORMATION_SCHEMA.TABLES"

    $connectionstring = "Server = 192.168.0.1; Database =xenapp6-datastore1; UID=testdomain\testuser; ;Integrated Security=true"
   
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $connectionstring

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SQLCommand
    $SqlCmd.Connection = $SqlConnection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
   
    $dsdatabasefound = $DataSet.Tables[0].Rows.Count

    $SqlConnection.Close()
}

test_ima_db

All replies (8)

_{Monday, December 13, 2010 12:57 PM}

You don't/can't specify the Windows (NT) login and password when using integrated security. Assuming you've launched PowerShell with your testdomain\testuser account you should use the following connection string:

$connectionstring = "Server = 192.168.0.1; Database =xenapp6-datastore1; Integrated Security=true"

_{Monday, December 13, 2010 8:13 PM}

yes.. in my next try i created a script which does the db connection and is started with start-process -credential...

so the 1st powershell script is starting the 2nd poweshell script and checking the return value. unfurtunatly where seems to be a problem with the "-wait" at "start-process" when the 1st script is run by a non-domain user.

example:

$username = 'domain\user'
$password = ConvertTo-SecureString "password" -AsPlainText -Force

$credential = new-object Management.Automation.PSCredential($username, $password)

$proc = Start-Process "notepad" -Credential $credential -PassThru -Wait

write-host "Returnvalue: "$proc.ExitCode

when script is started by a user belong to domain everything is fine. when the script is started by a local user -wait returns a "access denied", and proceed with the script before it can handle the return code.

Any idea why this can happen?

Thanks in advance!

Andreas

_{Wednesday, December 15, 2010 4:39 AM}

Not sure why adding the -wait  to start-process with -Credential causes access denied error, but I'm able to reproduce it. Since this is a new and unrelated question, I would suggest marking your original question as answered and posting a new question with an accurate title "Start-Process with -credential and -wait parameters fails..."

_{Wednesday, December 15, 2010 11:32 AM}

-wait seems to work for local admin users administrator.... -credentials doesn't work as sytem accont.

because this topic seems more system related i open a thread at http://social.technet.microsoft.com/Forums/en-US/winservergen/thread/fedfa367-1bc0-43e1-9f81-eaf4b5896874/#ec07a87c-3d19-4522-ade4-21e8d6070d9b

Cheers,

Andreas

_{Friday, December 17, 2010 5:46 AM}

Hi,

I tested your script. Start-Process works fine if "-wait" is not specified. Access Denied only occur when "-wait" was used. The parameter "-wait" suppresses the command prompt or retains the window until the process completes. This operation may require administrator rights.

Also "runas /user:w7\t1 notepad" works for me and it should work for you. To narrow down the cause, I suggest we try to monitor the process:

Process Monitor
http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx   

Download and run it on problematic system. Click File menu, check Capture Events, try to reproduce the error Access is denied, when the error occurs, uncheck Capture Events again. Exported events to Logfile.PML and upload the file to Windows Live SkyDrive (http://www.skydrive.live.com/). If you would like other community member to analyze the report, you can paste the link here, if not, you can send the link to [email protected] (with this thread title or link in the email. Please don’t share documents with this address).

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

_{Monday, December 20, 2010 2:19 AM}

Hi,

Have you tried the suggestions? Any update is welcomed. If there is any problem, please let us know the detailed error message.

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

_{Wednesday, August 5, 2015 1:19 PM}

I have a script that runs as SYSTEM, if i try to start-process notepad.exe it's working fine. if i add -credentials $cred it shows Access Denied. The credentials i pass over has local admin access, so why is there Access Denied? with procmon on powershell.exe i can not identify any access denied operation, i can see that powershell access notepad.exe with success result.

any ideas?

_{Wednesday, August 5, 2015 5:29 PM}

Please start you own thread.  This one was closed years ago.

\(ツ)_/