Question:
I am using a script that loads the following SQL Server 2008 R2 powershell plugins
|
1 2 3 |
Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 |
I then user invoke-sql like this:
|
1 2 |
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:
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