February 23, 2012 at 6:25 am
I have installed default instane sql server 2008 r2 and found error in sql log about SPN.
"The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. 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"
I have resigested the spn with below command,
setspn -A MSSQLSvc/server.doamin.com:1433 <sql service accountname>
but updating the command i have run below query in query window,
SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
OUT PUT:
net_transportauth_scheme
Shared memoryNTLM
It is still showing net_transport as "Shares memory" and auth_scheme as "NTLM" .
Note:only shared memory and TCP/IP is enabled,
Please suggest me .
February 23, 2012 at 6:40 am
Are you planning to use Kerberos authentication?
If not... dismiss.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 23, 2012 at 6:46 am
Even after restarting the server i am seeing same error in the error log.
i am not understanding whether spn is registered or not.
February 23, 2012 at 6:56 am
You will get that error as the account which SQL is running as doesnt have access to register/deregister the SPN. As pointed out above, if your not planning on using Kerberos authentication then you can ignore the error.
If you are grant the account running SQL the rights in AD or self register the SPN.
You can also do the below in a CMD window to list the SPN's for a server
setspn -L SERVERNAME
February 23, 2012 at 7:08 am
Hi have executed the command and i found the below information
"
C:\>setspn -L <SERVERNAME-A>
Registered ServicePrincipalNames for CN=<SERVERNAME-A>,CN=Computers,DC=<Domain>,DC=
com:
tapinego/<SERVERNAME-A>
tapinego/<SERVERNAME-A>.Domain.com
WSMAN/<SERVERNAME-A>
WSMAN/<SERVERNAME-A>.Domain.com
TERMSRV/<SERVERNAME-A>.Domain.com
TERMSRV/<SERVERNAME-A>
RestrictedKrbHost/<SERVERNAME-A>
HOST/<SERVERNAME-A>
RestrictedKrbHost/<SERVERNAME-A>.Domain.com
HOST/<SERVERNAME-A>.Domain.com
"
we are using kerberos
February 23, 2012 at 7:12 am
please read through this url, it will detail how to give permission to the SQL service account so it can register/deregister the SPN on startup and shutdown
http://technet.microsoft.com/en-us/library/cc773257%28v=ws.10%29.aspx
and this url
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply