During a lab setup of XenDesktop 7.6 I used a Microsoft SQL 2008 R2 instance which I installed before while setting up the rest of my lab environment. While the database setup worked seamlessly for other environments it seemed that I couldn’t access the SQL server from the XenDesktop Setup wizard.
I first tried the obvious things, using a service account didn’t help. After that I tried the SA account (just to see if it was an actual issue with the rights on the service account) but that didn’t work either.
I couldn’t create the database from the XenDesktop wizard, I tried several accounts but they couldn’t connect to the database or didn’t have the rights the access the database server.
Apparently changing the user didn’t had the effect I wanted, I logged on to the SQL just to make sure my SA password was still valid and it was because I was able to logon to the SQL management Studio with the SA credentials. Because I was already logged on the SQL server I just went on opening the event viewer and found the following errors:
My friend Google then found the following topic:SSPI handshake failed with error code 0x8009030c, which led me to the How to Configure an SPN for SQL Server Site Database Servers. It seems that during the installation of SQL the SPNs for SQL server weren’t registered.
Solving this issue:
With the command ‘setspn -L %hostname%’ you can list the SPNs that are registered for a certain server.
When I did this for my SQL server it didn’t list the SQL services so I had to register the SPN manually. Again I googled and found the following article:Register a Service Principal Name for Kerberos Connections.
This article described the following switches to manually register the SPN:
|To register the SPN manually, the administrator must use the Setspn.exe tool that is provided with the Microsoft Windows Server 2003 Support Tools. For more information, see the Windows Server 2003 Service Pack 1 Support Tools KB article.
Setspn.exe is a command line tool that enables you to read, modify, and delete the Service Principal Names (SPN) directory property. This tool also enables you to view the current SPNs, reset the account’s default SPNs, and add or delete supplemental SPNs.
The following example illustrates the syntax used to register manually register an SPN for a TCP/IP connection.
setspn -A MSSQLSvc/myhost.redmond.microsoft.com:1433 accountname
Note If an SPN already exists, it must be deleted before it can be reregistered.You do this by using the setspn command together with the -D switch. The following examples illustrate how to manually register a new instance-based SPN.For a default instance, use:
setspn -A MSSQLSvc/myhost.redmond.microsoft.com accountnameFor a named instance, use:
setspn -A MSSQLSvc/myhost.redmond.microsoft.com:instancename accountname
So I ran the command:
‘setspn -a MSSQLSvc/SQL001:1433 administrator’
The following screen output appeared:
After I registered the SPN for the SQL Server I listed the SPNs of the server again and the SQL service was registered. After a reboot I was able to connect to the database from the XenDesktop wizard.