How to access a SQL-Server with Pass-through, SQL-Server and given domain credentials

This week I tried to write a simple PowerShell script to connect and create a new database on a Microsoft SQL-Server. The script’s purpose is to create the new database in an automated Citrix XenApp 6 installation with a given domain-user account. The script will be started as a task in a task sequence. Because of a malfunction in SCCM vNext I can't start the task with a given domain service account. After some research I found out that there are three different ways to access a SQL Server with PowerShell. Especially the access with given domain credentials doesn’t work as I expected!

For all three steps you need to install SQLServerNativeClient, SQLSysClrTypes and SharedManagementObjects on our system (here).

The script body

The script body is the same for the following steps to access the server. At first we define two variables with the SQL-Server’s name and its instance. In this case we will try to connect to the server DIS-SHADOW-001 later.

Following we load the needed "Microsoft.SqlServer.Smo" assembly, which must be installed on your system, and create a new Microsoft.SqlServer.Management.Smo.Server object.

$varDBServer = "DIS-SHADOW-001"
$varDBInstance = ""
###
### Load the required assembly for sql server administration
### Requieres SQLServerNativeClient, SQLSysClrTypes and SharedManagementObjects
###
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$varSQLServer = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$varDBServer\$varDBInstance"

Access the SQL-Server with your current credentials (pass-through)

The easiest way is to use the credential for the current user who is running the script. Therefore you append the script with the following line:

$varSQLServer.ConnectionContext.LoginSecure = $true

To check if a connection can be established you need to execute an operation. For this blog I try to read the build number of the SQL-Server.

###
### Try connection to SQL-Server with given parameters
###
Write-Host "Try connection to SQL-Server with given parameters`n"
try {
          $varBuildOfSQLServer = $varSQLServer.Version.get_Build()
          Write-Host "Build of SQL-Server '$varDBServer' is $varBuildOfSQLServer"
} catch {
          Write-Host "ERROR: Cannot access SQL-Server '$varDBServer'. Exit script!"
          exit –1
}

The expected result is the build number and not an error message.

Access the SQL-Server with SQL-Server credentials in mixed mode authentications

Another easy way to access the SQL-Server is with SQL-Server credentials. For my test I granted an SQL user named "test" access to the SQL-Server. I use the same body, but with different parameter for the ConnectionContex:

 $varDBUser = "test"
$varDBPassword = "test"
$varSQLServer.ConnectionContext.LoginSecure = $false
$varSQLServer.ConnectionContext.Login = $varDBUser
$varSQLServer.ConnectionContext.Password = $varDBPassword

To check it, try to read the build number like before.

Access the SQL-Server with given domain user credentials

The third way was the most important way for me to access the SQL-Server. I had some tries but in the end I found the following combination (important: do not use a domain prefix like domain\user. It will only work with the username):

$varDBUser = "serviceDBAccessDom"
$varDBPassword = "_serviceDB1234567890"
$varSQLServer.ConnectionContext.LoginSecure = $true
$varSQLServer.ConnectionContext.ConnectAsUser = $true
$varSQLServer.ConnectionContext.ConnectAsUserName = $varDBUser
$varSQLServer.ConnectionContext.ConnectAsUserPassword = $varDBPassword

To check it, try to read the build number like before.

It’s amazing that it will not work with the domain-prefix.

Thanks to Timm for his support.

Update (2011/02/14)

Timm convinced me to correct 2 lines of this code. I changed from set_ConnectAsUserName (…) to ConnectAsUserName = „…“ (the same for ConnectAsUserPassword).

I also
added a script for download and testing.