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

Question

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

Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 

Then I call user-sql as follows:

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

I use the method to run a series of upgrade scripts in our databases. I pretty happily used this in our test environments, but then I tried it in production, and it turns out that we have a difference in server configurations. On our prod servers, named pipes are disabled for security reasons (apparently a worm attack), and our DBA does not want to enable it.

This is the error that I get, and the study says that this is a problem with named pipes - it starts working when I also turn them on.

INFO ERROR: Invoke-Sqlcmd: The 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 a way to switch my script so that it does not require named pipes? Or is it a built-in connection method for invoke-sqlcmd, and I need to change my identity (if so, then any suggestions).

Answer 1

This is a reasonable assumption. But here goes:

I think you need to "override the default" using the registry.

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

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

 Control Panel / ODBC Data Source / System DSN. 

Add Sql Server. (Not own client).

The most important button is the Client Configuration, where you can choose named-pipe or tcp / ip.
Try the DSN method, and after the wizard finishes, review the entries in the section

 HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\ConnectTo 

.........

You can check this out:

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

Find this code.

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

Answer 2

Like the Surreal response to using LPC (local shared memory) for TCP / IP instead of named pipes, you can also specify -ServerInstance tcp:foodb

Answer 3

You can change the connection method using prefixes on the instance name as for sqlcmd . With SQL Server 2012 and Powershell 4, this works for me:

 Invoke-Sqlcmd -Query $sqlQuery -serverinstance "lpc:localhost" -Database "myDatabase"