SQL Server – Detect Incoming Connection Using Alias

aliassql server

Using SQL Server Configuration Manager to Create a Server Alias for Use by a Client is cool tool used in many cases when it works

In my DBA position I do not have access to the application server, to see if the SQL Server Configuration Manager or the SQL Server Client Network Utility (not sure this is the correct link) was used to create an alias.

Is there any way I can use SSMS, or any logs, events, etc to identify that an incoming connection is connecting with an Alias?

Best Answer

No, that translation is done on the client side and so, with or without an alias, those connections are going to look the same to SQL Server. There is no evidence in DMVs like sys.dm_exec_connections or sys.dm_exec_sessions what name/IP/port combination was entered by the user to resolve to the server where SQL Server is running. SQL Server itself only cares that the connection was established.

Unless you can infer that anyone not using an alias is using shared memory or named pipes, therefore anyone using TCP must be using an alias. I can assure you that this is not a safe assumption.

Related Solutions

Sql-server – How to change SQL sever configuration manager settings using TSQL

Most settings that can't be done via sp_configure are registry based

So, you can use xp_instance_regread etc to change them. You'll have to find a list of registry keys yourself sorry but most are under HKLM\Software\Microsoft\Microsoft SQL Server

One examples , protocols is under

...
...(instancename)\MSSQLServer\SuperSocketNetLib\Tcp
...(instancename)\MSSQLServer\SuperSocketNetLib\Sm
...

and each of these has an Enabled value

One important note: you can stop SQL Server from xp_cmdshell or using SHUTDOWN but not start it of course...

SQL Server 2008R2 – Alias Not Working

Summarizing what we discovered in the chat.

When connecting over the network if the Windows Server is registered in Active Directory (AD) with a Service Principal Name (SPN), clients that try to connect with Integrated Authentication will get the Kerberos token from AD for the AD object that matches the server name to attach to the authentication. Kerberos won't be used when either the server that is being connected doesn't have an SPN or is being connected using SQL Authentication.

In this case, because "OLD-SQL" is still registered in AD and has an SPN registered spoofing, the DNS in the local host file won't work when using Integrated/Windows Authentication, as there is nothing adjusting which token is returned/used from AD. There doesn't appear to be a way to have the client know to use Kerberos token for "NEW-SQL" with the original "OLD-SQL" server still being in service, except to remove the "OLD-SQL" SPN from AD to force the authentication to fall back to NTLM. Spoofing the DNS works for aliases in which are not actual computer names registered in AD or do not have an SPN registered, as AD doesn't return a token for them, so the authentication falls back to using NTLM.

References

Understanding Kerberos Keys

Registering an SPN

Best Answer

Related Solutions

Sql-server – How to change SQL sever configuration manager settings using TSQL

SQL Server 2008R2 – Alias Not Working

Related Question