SQL Server 2012 Named Instance Connection Error spautoinstaller

12 Nov

While running autospinstaller, one error we came across was:

Exception calling “Open” with “0” argument(s): “A network-related or instance-specific error occurred while establishing a connection to SQL Server

I grabbed the PowerShell from the AutoSPInstallerFunctions.ps1 file so i could try a few different connections.  The PowerShell that was failing looks like:

$sqlServer = “localhost\sharepoint”
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection
$objSQLCommand = New-Object System.Data.SqlClient.SqlCommand$objSQLConnection.ConnectionString = “Server=$sqlServer;Integrated Security=SSPI;”$objSQLConnection.Open()

So the obvious failing point is when we open the connection, so I tried using a whole bunch of aliases (we even created new aliases)  and different ways of calling our sql server instance name. Sauron is the server name at this stage.

.\
localhost
sharepointalias
localhost\sharepoint
localhost\\sharepoint
sauron
localhost\sauron

None of these worked in any combination. So we made sure that port 1433 was open on the firewall and checked other things that may affect the connection. After a bit of thinking and playing around Ryan realised that it may be our named instance that is causing the problem. SQL was installed with an instance called “SharePoint”. He eventually determined that named instances have a dynamic port number, so when allowing port 1433 on the firewall, this did not help our case.

The easiest way / the way we decided to fix this was to re-install sql server with the default instance, and walaa all our problems went away. But like I said this is a pretty lazy way of fixing this. To get around it you need to create an alias either manually (using SQL server configuration manager) or by adding it into the first xml item in your config xml for “DBAlias” like below, you will need to find out the dynamic port number for either of these cases.

<DBAlias Create=”true”
DBInstance=”sharepoint”
DBPort=”1846″ />    <—- random port number

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: