SQL Connectivity

Till 2005 version of SQL, NTAUTHORITY\ADMINISTRATORS was there. It was there, because this group knew who are the administrators on the system. We didn't need to create a group or user here. Whoever would be the administrator of windows will be able to enter SQL Server without any login creation.

But customers did not wanted it, so it was removed in 2008 version. So, now if customer wants to not have this feature in 2005, we have to delete NTAUTHORITY\ADMINISTRATORS and then create login for whatever user they want explicitly.

Protocols of SQL Server(For Network)

• TCP\IP(Transmission Control Protocol/Internet Protocol)
• NP(Named Pipes)
• LPC(Local Process Communication) or Shared Memory
• VIA(Virtual Interface Adapter)-depleted or very rarely used

Shared Memory or LPC

When client is present on the same machine where SQL Server is installed. It means client and server both are on the same box.

Named Pipes

It is used only in case of intranet.

TCP\IP

It is used for connecting through internet or intranet. There are two choices available in this case:

• Dynamic Port: SQL Server chooses port when it starts.
• Static Port: User can give port no. of his choice.

If we have enabled or disabled protocols((TCP/IP, NP,LPC, VIA) any of them), then you need to check  that whether SQL Server is following it or not. We can check that in errorlog file.

To enable or disable protocols, you can go to Configuration Manager-> SQL Server Network Configuration-> protocols for MSSQLServer.

Now a question arises as to how will an application know that which port no. it has to hit?

The answer to it is that there will be a SQL Server Browser service running on server and this runs on 1434. All client requests come on 1434, then it tells the client computer that you have to send your request on whatever port no. it tells and then the client computer sends to that port no.

Authentication Mechanism

There are basically two types of authentication mechanism in windows:

• NTLM: NT LAN Manager authentication verifies the connection whether the user trying to enter is authorised or not. NTLM is located in kernel(Active Directory)
• Kerberos authentication: It is the most secured protocol in the world. There are some requirements that need to be fulfilled in order to use this protocol. They are:

1. The protocol enabled should be TCP.
2. Windows Authentication should be there.
3. System should have windows server 2000 & up as O.S.
4. It should have domain login.
5. SPN should be there.

*SPN(Service Priniciple Name)-Unique name by which any service that is running on any machine inside the domain can be uniquely identified.

Syntax for SQL Server Service for SPN:

MSSQLSVC:S1\I1,Port no.,Service Account

MSSQLSVC tells an AD that which machine is running on which port and which instance, where S1 denotes server name, I1 denotes instance name and service account denotes the name of the user ex:PC1\Administrator.

Kerberos authentication is secured as you only need to send the password only once on the wire.

How it works in Kerberos Authentication?

1. AD will enable Kerberos
2. You will request AD
3. AD will check SPN & authentication
4. Generate ticket for a particular time slot e.g. 2 hrs, 24 hrs, 48 hrs etc. So this way password is sent one time on the wire or else if kerberos will not be there, then whenever one logs out he needs to login again but due to kerberos, for the ticket time you don't need to give password everytime.
5. Then finally the connection gets established to server.

All these five steps happens virtually.

Advantages of Kerberos:

1. Mutual authentication
2. Secure authentication tickets which are encrypted and no pwd is included
3. Integrated authentication

SPN is written everytime SQL Server starts, service account goes to AD and writes it. It needs to have read SPN and write SPN permission.

What happens to old SPN?

When service stops, service account goes to AD and destroys it.

Probabilities:

1. No SPN: Old SPN has been deleted, new has not been generated yet and no permissions have been assigned.

2. Wrong SPN: Old SPN not deleted yet and new user does not have read and write permissions of SPN.

3. Multiple SPN: Old SPN has not been deleted but ID has been deleted and new ID has got read and write permissions and meanwhile SQL starts. It results in multiple SPNs.

If any of these conditions arrive, kerberos will not start.
The error that we get in these situations is as follows:

"Cannot generate SSPI content"
SSPI stands for Security Support Provider Interface

Utilities for setting SPN:

1. SETSPN -L pcname [setting SPN]-> No SPN condition
2. SETSPN -D old SPN [deleting wrong SPN]-> Wrong SPN condition
3. SETSPN -D old SPN [ deleting the SPN which is not required]-> Multiple spN condition

These utilities re ran on command line and they work only in domain.

Resolution of some SPN related problems:

• If SPN problem persists, you can contact the AD guy and tell him to negotiate the NTML.
• If you have only the options to go for NTML or kerberos and you have no time to contact AD, you can switch it to Named Pipes.
• If you have to connect to TCP/IP and its not working for some or the other reason then you can either reverse the order or you can create an alias if clients are in huge amount.
• If client is at some remote location then you can tell him to go to management studio and write NP: before the server name when he logins to SQL Server.

Changing the order:

Go to Configuration Manager and click on SQL Native Client-> Client Protocols->Order

Creating the alias:

Go to configuration manager-> Alias->New Alias-> Give the server name in new alias-> Protocol-> NP->Ok

System & Application Log:

System log is the log about errors of system and Application log logs error of the applications.

If SQL Service browser is not running, you can check it in application log, which is located in start->administrative tools->event viewer->system and application

Just to be sure that what port no. you have given to the service has been followed by it or not, we can always check it using command line.

netstat -aon

This shows which service has occupied the port no. on which SQL server was ordered to connect.

Another method when SQL Server Browser service is not running, then n connection window of SSMS you can mention Server name as "ServerName\InstanceName, port no."

*Default instance runs on 1433 port no.

DMV:

DMV stands for Dynamic Management Views which are the read only views and are window to the table.

Examples:

1. select * from sys.dm_exec_connections-> to know what authentication protocol is used

2. select * from sys.dm_exec_requests where session_id<40

3. select status, * from sys.dm_exec_sessions where session_id>50