Sql-server – How to connect Powershell to the SQL Server named instance

powershellsql serversql-server-2012

When I run 

# Load the SMO assembly 
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo"); 
$ServerName = "[local\s12]"; 

#Get a server object which corresponds to the **default** instance 
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName

I am getting an error

Failed to connect to server local\s12.

Best Answer

$ServerName = "[local\s12]";

Remove the square brackets.

Apart from that, the PowerShell code looks fine (and works on my test machine - the code I used is below, copied from my SQL Server test machine SERVER1).

PS C:\Users\Administrator.ADVENTURE-WORKS> [void][reflection.aSSEMBLY]::LoadWithPartialName("Microsoft.SQLServer.Smo" )
PS C:\Users\Administrator.ADVENTURE-WORKS> $ServerName = "server1\sql2012"
PS C:\Users\Administrator.ADVENTURE-WORKS> $ServerName
server1\sql2012
PS C:\Users\Administrator.ADVENTURE-WORKS> $Srv = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server $ServerName

Additionally, is the server actually called "local" or is that just anonymised? If you want to connect to the local server then the alias is a period. 

$ServerName = ".\SQL2012"

Otherwise, try to connect to the server local\s12 using SQL Server Management Studio using windows authentication from the same machine that you are running the powershell script. if that fails then there is a connectivity issue. Check firewalls (on the SQL Server machine and on any routers in between the client and server). Check that the TCP/IP network library is enabled on both the client and the server. If TLS is enabled then confirm that the certificates are all correct.

Related Question