SQL Server - How to activate SPN for SQL? - Asked By Gokhan Metepe on 28-Feb-12 02:51 AM

I am trying to activate SPN with that codes and getting this errors? whats the problem?

The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

setspn -A MSSQLSvc/**.***.***.**:1433 <MachineName>\<SQLUserName>

Somesh Yadav replied to Gokhan Metepe on 28-Feb-12 03:34 AM

When the SQL Server service account is configured to use the local system account, the server will automatically publish the SPN for you. However, a SQL Server best practice is to change the startup account from local system to a domain user account to better secure the SQL Server instance. If you're using a domain user account to run the SQL Server service, you have to manually create the SPN for the account in Active Directory. Once created, you can view the SPNs registered using an ADSIEdit console.

Note: To use the SetSPN utility, or to open an ADSIEdit MMC console, you must first install the Microsoft Windows Server support tools. These tools are included in the support tools folder on both Windows 2000 Server and Windows Server 2003 CDs. To install the Windows Server support tools, navigate to \SUPPORT\TOOLS\ on the server's installation CD and run suptools.msi.

To register the SPN for the domain user account in Active Directory for the default instance of SQL Server (assuming you haven't changed the port it is listening on) you can use the following syntax:

Setspn -A MSSQLSvc/<SQL Server name>:1433 <domain>\<user>

The trick here is that you have to do this twice. You need to register the SPN for both the SQL Server computer's NetBIOS name and FQDN to allow Setup to succeed and for the site to operate properly after it is installed.

When using a SQL Server named instance to host the site database, and using a domain user account as its start up account, you must register the SPN for the named instance in Active Directory. When registering the SPN for a SQL Server named instance, the syntax is the same as creating one for the default instance--the named instance is detected by the port number it responds to and is not specified as part of the SQL Server name (just like IIS and the default Web site, you can't use the same port that the default instance uses for a named instance of SQL Server).

So, the command to register the SPN for the domain user account running a named instance using port 1400 (1400 is just a random port I picked out of my head, not a recommendation) would be:

Setspn -A MSSQLSvc/<SQL Server name>:1400 <domain>\<user>

Once again, you have to do this twice, once for the SQL Server's NetBIOS name and once for the SQL Server's FQDN.

Troubleshooting Tips
There is a known issue when running the stand-alone prerequisite checker on named instances in case you see an error when running that that says you must specify a valid NetBIOS host name. To test the named instance before beginning the install, you can use the Setup command line:

Setup /Prereq /Pri /SQL <SQL Server Name\Instance Name>

Also, when running Setup to install the site database on a remote SQL Server named instance, the SQL Server browser service must be running when Setup goes looking to validate it or else Setup will fail.

Gokhan Metepe replied to Somesh Yadav on 28-Feb-12 03:55 AM

SQL Server is on my office and i got static IP for it. When i go to my customer i want to connect with my notebook to server pc at office. I dont have any web page. What is domain name and account name? I have tried it with Ip adress instead of domain and SQL User name instead of account. And i am still getting error: 

FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0x00000525
 Could not find account <username>

Somesh Yadav replied to Gokhan Metepe on 28-Feb-12 03:59 AM
Have you configured delegation? http://technet.microsoft.com/en-us/library/ee675779.aspx
Somesh Yadav replied to Gokhan Metepe on 28-Feb-12 04:00 AM

I'm just brainstorming here, but this might be a Service Principal Name issue. Have you set this on both nodes of the WNLB.

For Kerberos authentication to work, Service Principal Names (SPNs) must be registered for IIS

When using IIS 6.0 or 7.0 for icon or OSD file retrieval and streaming of packages, for Kerberos authentication to be enabled, the SPNs must be registered as follows:

  • On the IIS server, run the following commands by using the SETSPN.EXE Resource Kit tool. The server fully qualified domain name (FQDN) must be used.

    Setspn -r SOFTGRID/<Server FQDN>

    Setspn -r HTTP/<Server FQDN>
See: http://technet.microsoft.com/en-us/library/cc817171.aspx

Gokhan Metepe replied to Somesh Yadav on 28-Feb-12 04:20 AM
That article for Windows Server 2003, Windows Server 2008, or Windows Server 2008 R2. I am using Win 7 and in Win 7 users can not write codes for appcmd.exe. When i tried, i am getting error.
Gokhan Metepe replied to Somesh Yadav on 28-Feb-12 04:24 AM
I have tried your codes, and get same error.
Asked By Gokhan Metepe on 28-Feb-12 08:26 AM
I have tried all of themn but i dont have domain.