Welcome to SqlServerFaq.net

 

 

In sql server the default instance has a listener which listens on the fixed port which is TCP port 1433. However for the named instance the port on which the sql server listens is random and is dynamically selected when the named instance of the sql server starts.
So when we connect to the default instance of the sql server the connections goes to the TCP port 1433 while when we connect to the named instance of the sql server the connections goes to the sql browser service which listens on the UDP port 1434. The sql browser service replies to the client with port no. on which the named instance of the sql server listens at that point of time and then the client is redirected and connects to the port no. provided by sql server service.
You may require to change the dynamic port of the named instance to the static port no. so to implement Firewall in your organization so that clients connect to the sql server through the firewall.
For Standalone instance of the sql server we can change the dynamic port of the named instance to the static port by using sql server configuration manager in sql 2005. In order to change the port we need to the sql server configuration manager.
Start->Program Files-> Microsoft sql server 2005->Configuration Tools->sql server configuration manager.

In configuration manager console, on the left hand panel, expand the sql server 2005 Network Configuration.

Click on the”Protocols for <Named-Instance-Name>”. On the right hand panel we need to right click and go to properties on the TCP/IP.

Click on the Ip Addresses tab on the top and keep the “Dynamic ports” row BLANK and write the desired port no. on which you want the named instance to listen on “TCP ports” row in the “IP ALL “section.

After performing these activities we need to restart the sql server services for the changes to take affects.

After restarting the service you can confirm that whether changes has taken affect by checking the following registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name> \MSSQLServer\SuperSocketNetLib\Tcp
This key should contain the port no. defined by you.

However in case of the cluster, many a times (In particular when sql server 2005 32 bit is installed on the 64 bit system) it is observed that when we change the port no. of the named instance to the static port using the method described above the port no. again changes back to the dynamic port after you restart the services.

This is behavior can be explained as follows

When we changed the dynamic port of the sql server to the static port and stopped the sql server service the local copy of the registry which contains the dynamic port no. gets checkpointed to the quorum.

Now when the service is started the Checkpointed value from quorum are copied back to the registry and hence in spite of changing the value to static port. The value was replaced by the dynamic port from quorum.

So in order to change the dynamic port of the server to the static port in the clustered named instance of the sql server 2005 we need to follow the following steps

1) Take the sqlserver service offline from cluster administrator

2)  Disable the checkpointing to the quorum using the following command

Cluster res “SQL Server (TEST2005)” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER”

3) Change the Dynamic port of the sql server to static port  on all the nodes using the method described above.

4) Enable the checkpointing to the quorum using the following command

cluster res ” SQL Server (TEST2005)” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER”

5) Bring the sql server service online

In the above example “TEST2005” is the name of my sql server instance
                                          MSSQL.2  is since it is second sql server 2005 instance installed on the system

You will observe that the dynamic port is changed to the static port.

While installing SP2 on the sql server 2005, the installation may fail. Whenever the sql server installation fails it creates a log in the following location.
C:\Program Files\Microsoft Sql Server\90\Setup Bootstrap\LOG\

The above location generally contains following folders and file.
1) Files                (Contains Logs for the installation of the RTM (Release to Market) version of the sql server)
2) HotFix             (Contains Logs for the installation of the Hotfixes and service packs)
3) Summary.txt   (Contains summary of all the sql server products which were installed and                               whether they  Successfully installed or Failed) 

                                  
These logs are created for any sql server 2005 setup installation which applies to the installation of the RTM version or any of the service packs and even all the editions of the sql server 2005 viz Standard Edition, Express Edition, Enterprise Edition.
Whenever the sql server 2005 setup fails we need to check the summary.txt located in the above location to get the information for cause of the failure and to know which products were unsable to install.
So during the installing of the sql server 2005 sp2 if the installation fails and when you look at the summary.txt, you may find that one of the cause of the failure is ERROR 29506.
The summary.txt may have the following information
———————————————————————————-
Product                   : Database Services (MSSQLSERVER)
Product Version (Previous): 1399
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number              : 29506
Error Description         : MSP Error: 29506  SQL Server Setup failed to modify security permissions on file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that  exists on the destination drive.

 

When you look into the file C:\Program Files\Microsoft SQL Server\90\Setup  Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log you will see the following error

 

Configuring ACL:
 Object: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data   ACL: (A;OICI;FA;;;[SQLServer2005MSSQLUser$CORPDBPRO$MSSQLSERVER])
Action: 0×103
Failed ACL:
  SetSecurityFileDescriptor is failed at the error code 5; Converted SDDL: ‘(A;OICI;FA;;;S-1-5-21-3714694515-4057322299-1779769068-1013)’
        Error Code: 0×80077342 (29506)
Windows Error Text:   Source File Name: sqlca\sqlsddlca.cpp
Compiler Timestamp: Wed Jun 14 16:27:11 2006
     Function Name: ExceptionInSDDL
Source Line Number: 65

 

The main cause of the above error is that there is improper permissions of the DATA and LOG folder of the sql server 2005. In other words the DATA and LOG folders didn’t have Full Control permissions for the user Administrator and as a result the error message says
MSP Error: 29506  SQL Server Setup failed to modify security permissions on file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ for user Administrator

In order to avoid the error we need
1) To start the SP2 installation using an administrator or a user with administrative privileges.

2) All the files and the folder contained in the DATA and LOG must have Administrative privileges with FULL CONTROL permissions.
In order to achieve this right click on the DATA and LOG folder go to sharing and security. Click on the security tab and make sure Adminstrator is present and it has FULL CONTROL. Also go to the advanced tab and click on inherit the permissions to the child objects.
Finally confirm that all the folders and files located inside DATA and LOG folder have Full Administrator priviledge.
Once the permission issue is resolved we can restart with the installation and it will work fine

Reference
http://support.microsoft.com/kb/916766/en-us

Kerberos is a network authentication protocol which can be used only with TCP/IP protocol. So if the client connects to the sql server with the Named Pipe Alias Kerberos is not used. Every service that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. If an SPN is not set for a service, then clients will have no way of locating that service. Without properly setting the SPNs, Kerberos authentication is not possible.

For more information on SPNs, please refer to the below article,

http://msdn.microsoft.com/en-us/library/ms677949.aspx

 

To use Kerberos authentication, you must make sure that all the following conditions are true:

•	Both the server and the client computers must be members of the same Windows domain or members of trusted domains.
•	The server’s service principal name (SPN) must be registered in the Active Directory directory service.
•	The instance of SQL Server 2005 must enable the TCP/IP protocol.
•	The client must connect to the instance of SQL Server 2005 by using the TCP/IP protocol. For example, you can put the TCP/IP protocol at the top of the client’s protocol order. Or you can add the prefix “tcp:” in the connection string to specify that the connection will use the TCP/IP protocol.

What is SPN?

Service Principal Name (SPN) is the identity of a service which is started with the particular Domain Account running in that domain. SPNs are registered at the Domain Controller (Active Directory).

 To register the SPN at the AD we need to provide the following information

1)       The service

2)       The Fully Qualified Domain Name (FQDN) of the system on which the service is installed

3)       The TCP/IP port no. on which the service is listening. (Since we can have multiple services of same type running on the same system but on the different host for e.g  sql server)

4)       The startup account with which the service is started

SPNs are used by the clients who are connecting to that sql server service using windows integrated authentication mechanism

 When SQL Server is running under the local system account or under a domain administrator account, the instance will automatically register the SPN in the following format when the instance starts:

MSSQLSvc/<FQDN>:<tcpport>

 

C:\Program Files\Resource Kit>setspn -L pariks-2003

Registered ServicePrincipalNames for CN= pariks -2003,OU=Workstations,OU=Machines,DC=testdc,DC=corporate,DC=sampledomain,DC=com:

MSSQLSvc/ pariks -2003.testdc.corporate.sampledomain.com:1393

SMTPSVC/ pariks -2003

SMTPSVC/ pariks -2003.testdc.corporate.sampledomain.com

HOST/ pariks -2003

HOST/ pariks -2003.testdc.corporate.sampledomain.com

 

You must manually register the SPN for the instance under a domain administrator account to use Kerberos authentication. To register the SPN, you can use the SetSPN.exe tool that is included with the Microsoft Windows 2000 Server Resource Kit. This tool is also included with the Microsoft Windows Server 2003 Support Tools. The Windows Server 2003 Support Tools are included in Microsoft Windows Server 2003 Service Pack 1 (SP1).

You can use a command that is similar to the following to register an SPN for an instance:

SetSPN –A MSSQLSvc/<ComputerName>.<DomainName>:1433 <AccountName>

If an SPN already exists, you must delete the SPN before you can reregister it. You may have to do this if the account mapping has changed. To deleted an existing SPN, you can use the SetSPN.exe tool together with the -D switch.

After you connected to an instance of SQL Server 2005, run the following Transact-SQL statement in SQL Server Management Studio:

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

If SQL Server is using Kerberos authentication, a character string that is listed as “KERBEROS” appears in the auth_scheme column in the result window

In order to verify that Kerberos is setup and working fine we can use KerbTray utility. Kerbtray utility can be downloaded from

http://www.microsoft.com/downloads/details.aspx?FamilyID=4e3a58be-29f6-49f6-85be-e866af8e7a88&DisplayLang=en

How to use KerbTray utility

1)       Install the KerbTray utility from the above link and install it in C:\Program Files\Resource Kit

2)       On Installing it a GREEN icon appears on the Task Bar on the right hand bottom of the screen

3)       Right Click on the icon and click purge tickets to purge all the tickets received by that machine

4)       Wait until the green icon turns yellow. When the icon turns yellow fire the following command from the cmd prompt

C:\Program Files\Resource Kit>net session /d

The command completed successfully.

 

5)       Now we can request the ticket from the AD using the following command from the cmd prompt

                 C:\Program Files\Resource Kit>set L   (Press Enter)

                 lib=C:\Program Files\SQLXML 4.0\bin\

                 LOGONSERVER=\\comp-dc-01

                C:\Program Files\Resource Kit>net use \\ comp-dc-01\IPC$

                 Local name

                 Remote name       \\ comp-dc-01\IPC$

                 Resource type     IPC

                 Status            OK

                 # Opens           0

                 # Connections     1

                 The command completed successfully.

 

**comp-dc-01 is the DC for the domain.

 

After firing the above command wait for some time and you will see that yellow icon of the KerbTray turning green which indicates that it has started received Tickets which can also be confirmed by right clicking on the icon and click on LIST TICKETS

909801 (http://support.microsoft.com/kb/909801/) How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005

**To enable Kerberos logging in the Windows Xp, 2003, Vista as well as Windows 2000 (Reboot required after setting the following registry key), we need to set the following registry key

1. Start Registry Editor.
2. Add the following registry value:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\ParametersRegistry Value: LogLevel
Value Type: REG_DWORD
Value Data: 0×1

If the Parameters subkey does not exist, create it.

3. Quit Registry Editor. The setting will become effective immediately on Windows Vista, on Windows Server 2003, and on Windows XP. For Windows 2000, you must restart the computer.

 

 

** IMPORTANT : This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

Also please contact the Product Support Services for more help regarding the specific issue you have.