I have a problem where I cannot connect to a SQL Server using domain credentials. Using the SA credentials it works and the query runs as expected. But when I use domain credentials, I get an error.
Here is the script:
$SQLServer = 'server.domain.com'
$SQLDBName = 'DBname'
$username = "DOMAIN\user"
$password = "password"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
#--> With SA, it works, with DOMAIN creds, it does not
#$SqlConnection.ConnectionString = "Server=$SQLServer; Database=$SQLDBName; User ID=sa; Password=SApassword;"
$SqlConnection.ConnectionString = "Server=$SQLServer; Database=$SQLDBName; User ID=$username; Password=$password;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = 'select count (*) from my.table'
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
And here is the error I get when I run the script:
Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while
establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
At C:\scripts\PowerShell\myscript.ps1:28 char:1
+ $SqlAdapter.Fill($DataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
I should point out the fact that I am connecting to a SQL Server from a computer which is NOT in the domain (therefore I cannot use any sort of authentication passthrough).
Any ideas as to why I cannot connect with my domain credentials? I've followed other posts which offer advice on checking connections, firewalls etc. That's all in working order and the script runs perfectly as the sa (local) user. Just not on the domain..
If you have SQL Credentials use something like this:
$ConnectionString = server=*serverName*;database=*databaseName*;user id=*userName*;password=*passWord*;
If you have Domain Credentials use something like this:
$ConnectionString = "server=*serverName*;database=*databaseName*;user id=*domain\username*;password=*passWord*;trusted_connection=true;"
This works in my environment. Of course after that you do:
$Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
You cannot connect with domain credentials THAT WAY.
Domain credentials are present in the process making the connection. The account using in the process (or if using the NETWORK ONLY option in RUNAS) will be used to make the connection to SQL Server using integrated security.
Of course, because your process CANNOT actually run as a user on that domain because your computer is not in a trust relationship with that domain, I recommend you look at using RUNAS /NETONLY.
This will prompt for a password, and so you may have to write your own replacement for it which uses the CreateProcessWithLogonW API instead.
https://stackoverflow.com/a/758805/18255
This will allow your process to use domain credentials from the other domain which are only used on network connections and verified at that time, while still using the local account for other things. This MAY cause problems getting to other network resources which might rely on your local (or other domain?) account, I haven't fully tested how RUNAS /NETONLY works if you make DIFFERENT network connections from the same process.
Function SQL_CONN1 ($Query) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=myPC;Database=myDB;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $Query
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$a=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] }
SQL_CONN1 "Select * from [MyTable]"
Try
$SqlConnection.ConnectionString = "Server=myPC;Database=myDB;Integrated Security=True"
If you are not worried about security you can do it like this (not secure, though):
#Set variables here
$connectionString="server=$s;database=$sqlDbName;user id=$userName;password=$passWord";
$sqlConnection=New-Object System.Data.SqlClient.SqlConnection($connectionString);
It works in SQL Server 2012. But, again, not secure. Hope that helps someone...
You cannot pass username and password as string.
Try something like this -
#$cred = Get-Credential
# this will prompt for username and password, fill them in
# use this in your connection string
$secpwd = ConvertTo-SecureString $password -AsPlainText -Force
$SqlConnection.ConnectionString = 'Server=$SQLServer; Database=$SQLDBName; User ID=$username; Password=$secpwd;'
