How can I use invoke-sqlcmd without enabling named pipes?

Question

I am using a script that loads the following SQL Server 2008 R2 powershell plugins

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

I then user invoke-sql like this:

Invoke-Sqlcmd -Query "select * from table" -ServerInstance xyz -Database abc -username xxxxxx -password yyyyyyy

I am using method to run a number of upgrade scripts on our databases. I was quite happily using this in our dev\test environments but then we I tried it in production and it turns out we have a difference in server configurations. On our prod servers named pipes are disabled for security reasons (apparently worm attacks) and our DBA's don't want to enable.

This is the error I get and research says it is a named pipes problem - starts working when I enable them too.

INFO ERROR: Invoke-Sqlcmd : A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Does anyone know if there is some way to switch my script so that it does not require named pipes? Or is this the built in connection method for invoke-sqlcmd and I need to change tack (if so any suggestions).

Answer 1

Similar to Surreal's response to use LPC (local shared memory), for TCP/IP instead of named pipes you can also specify -ServerInstance tcp:foodb

Answer 2

This is an educated guess. But here goes:

I think you have to "override the default" by using the registry.

http://support.microsoft.com/kb/229929

Now, the easiest way to do this (IIRC) is to go through your

Control Panel / ODBC Data Source / System DSN.

Add a "Sql Server". (Not the native client ones).

The most important button is the "Client Configuration" where you can pick named-pipes or tcp/ip.
Try out the DSN method, and after completing the wizard, look at the registry entries under

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\ConnectTo

.........

You might check out this:

http://sev17.com/2012/11/05/cloning-sql-servers-to-a-test-environment/

Look for this code.

sqlcmd -S myCMServerInstance -d msdb -Q $query -h -1 -W |
foreach { Set-ItemProperty -Path 'HKLM:SOFTWAREMicrosoftMSSQLServerClientConnectTo' -Name $($_ -replace 'TEST') -Value "DBMSSOCN,$_" }

}