How to connect Powershell to the SQL Server named instance?

Posted on

Question :

When I run

# Load the SMO assembly 
$ServerName = "[locals12]"; 

#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 locals12.

Answer :

$ServerName = “[locals12]”;

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:UsersAdministrator.ADVENTURE-WORKS> [void][reflection.aSSEMBLY]::LoadWithPartialName("Microsoft.SQLServer.Smo" )
PS C:UsersAdministrator.ADVENTURE-WORKS> $ServerName = "server1sql2012"
PS C:UsersAdministrator.ADVENTURE-WORKS> $ServerName
PS C:UsersAdministrator.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 locals12 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.

Leave a Reply

Your email address will not be published. Required fields are marked *