Forum Discussion
Passing credentials to SQL connection string
Hello. I am new to PowerShell and would really appreciate some help!
I have a working script (Example A) which allows me to connect to SQL servers in my current domain using windows authentication. From there I can run a query and output the results to a file. Pretty handy!
The problem arises when I try to connect to a SQL server in a separate domain - since PS by default uses the creds of the logged on user, I am not sure of how to work around this. I have added the $username = Read-Host "Enter your username" (Example B) etc. to the beginning of the script but have been unsuccessful in getting this to work.
Can anyone tell me how I might modify my working script to accommodate this change? I am pretty certain my connection string needs to be modified but I can't get it to work.
Example A
$ServerInstance = "SQLServer"
$Database = "database"
$ConnectionTimeout = 30
$Query = "SELECT *
FROM database"
$QueryTimeout = 120
$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables | Out-File -FilePath C:\file.csv
Example B
$username = Read-Host "Enter your username"
$secureStringPwd = Read-Host -assecurestring "Please enter your password"
$ServerInstance = "SQLServer"
$Database = "database"
$ConnectionTimeout = 30
$Query = "SELECT *
FROM database"
$QueryTimeout = 120
$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables | Out-File -FilePath C:\file.csv
Hi
I am not a SQL expert but let me tell you what I think
It's not a the PowerShell use the logged-in cred to auth, its the connection string and what parameter support
You cannot pass the username and password through a connection string as Windows Auth. it wont work
The other way is to enable SQL Auth on the remote server and connect using SQL Auth
One way I think you can do it is to invoke Enter-PSSession and execute the SQL query on the remote server, But still not sure if it will work like this. Its just an idea.
- Good to know, thanks for the info!
You can't specify alternate Windows credentials in a connection string. When you leverage the integrated authentication option, the credentials of the host process are used.
Which brings me to some simple alternatives to worrying about the connection string:
- Launch a new PowerShell shell using "run as a different user" and use the appropriate credentials from the remote forest (assuming the forest trusts facilitate doing this);
- Use Invoke-Command, New-PSSession, Start-Process, etc. with the -Credential parameter to run the script under the context of the account from the remote forest;
- Use an SQL account, not a Windows account, in the connection string.
I'd probably go with the first option if I was going to do a few things against the server. If it was just running the one command, I'd probably go with Invoke-Command, but to each their own.
I mention point 3 for completeness, but really, it's a rubbish solution. There really shouldn't be common SQL-defined logins across multiple SQL hosts.
Cheers,
Lain
- Thank you Lain for the detailed response. I will see what I can accomplish with the Invoke-Command.
- I'd recommend looking at the DbaTools module as that includes an Invoke-DbaQuery function which, like most of its hundreds of other funnctions, allows you to pass in credentials as a parameter for authentication at the SQL Server as your chosen domain user
