I have the most simple of questions:

I've got a Microsoft SQL Server with two instances, default and a named instance called Instance1.

The default instance is running on static TCP/IP port 1433 and the named instance is running on static TCP/IP port 5555.

I'm trying to create an alias to Instance1 and went to SQL Server Configuration Manager and under SQL Server Native Client 11.0 Configuration (32 Bit) I created an alias as follows:

  • Aliasname: TEST1
  • Portnumber: 5555
  • Server: <ServerName>\Instance1

Then, directly on the SQL Server box I tried the following from a cmd:

  • sqlcmd -S TEST1 ... and I'm connected

Then I did:

1> select name from sys.sysdatabases
2> go

and I get the DBs that exist on the default instance, rather than Instance1.

I've tried changing the alias port to a random port, anything at all, and it will always connect to the default instance, I don't get it.

There are no MSSQLSvc/ SPNs at all registered for this server, but I can't imagine that having something to do with it.

Basically, no idea what's going on here.

This is a Windows Server 2016 and SQL Server 2016.

  • Please update your question with the picture of your Configuration Manager opened under Server Network Configuration for your DEFAULT instance where we can clearly see port number indicated under IPAll – sepupic Apr 2 '19 at 17:43
  • P.S. You should not include Instance name under SERVER when create the alias, it should look like this: Server: <ServerName>, Instance name is just ignored as there is port number set. – sepupic Apr 2 '19 at 17:45
  • Alternatively, you can update your question with the initial part of DEFAULT instance error log where it says on what ports it's listening – sepupic Apr 2 '19 at 17:46
  • Have you tried identifying the instance by methods that explicitly tell you the name rather than relying on the database set shown? For instance, by running either SELECT @@SERVERNAME; or SELECT SERVERPROPERTY('ServerName');? Just to make sure there's no mistake. – Andriy M Apr 2 '19 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.