January 21, 2011 at 1:22 pm
Hello Experts,
I was given the task of collecting information from all of our MSSQL Server boxes for a report. One piece of information I need is whether a server is using Kerberos or NTLM authentication. There are approximately 50 servers. Is there a way to do this without looking at each server individually? Maybe some type of looping t-sqlcode?
thx
-J
January 21, 2011 at 1:28 pm
Here is a starting point.
You may want to throw this into a powershell script or ssis package and return the results with servername back to a central reporting table.
Use Master
GO
SELECT
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 0 THEN 'Mixed Mode - Allows Both SQL Server and Windows Authentication Mode'
WHEN 1 THEN 'Allows Only Windows Authentication Mode'
END AS [Currently Used SQL Server Authentication Mode]
GO
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 21, 2011 at 1:47 pm
thx, this was helpful
January 21, 2011 at 1:56 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 21, 2011 at 6:26 pm
The answer was correct but I'm not sure it was to the question asked. Do you want to know whether Windows or SQL Login, or do you want to know NTLM vs. Kerberos.
The latter would be like this:
SELECT s.login_name, c.auth_scheme, s.HOST_NAME
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
And to the question asked, you should know a properly configured SQL server on a domain will generally be able to do both sorts. For example the output of the above on my server just now (truncated a bit) is:
login_name auth_schemeHOST_NAME
LHE\MSSQLServicesNTLM LHE-BDS
LHE\fergusonl KERBEROSLT-FERGUSONL
January 25, 2011 at 10:15 am
very helpful, this was a little more of what I was looking for 🙂
January 27, 2011 at 7:06 am
you need to have properly configured SPNs to be able to use Kerberos, connections that fail via kerberos or are unable to use it will default to NTLM. Ferguson's query shows how to query SQL Server and ascertain which transport is used by a client connecting to SQL Server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2011 at 7:16 am
While we are on this subject one little documented issue is when people try to use domain accounts (but not domain admin accounts) as the service account for SQL. That defeats kerberos and in some cases might prevent remote access to the machine at all (e.g. if the connecting system insists on it).
The fix to that is a bit arcane, you do this on a machine with domain tools:
dsacls "CN=Somecacount,OU=Service Accts or wherever,DC=YourDomain,DC=com" /G SELF:RPWP;"servicePrincipalName"
You have to look up the object DN for the service account (that is the first thing in quotes) and the last part you do literally as shown. This permits SQL to register the SPN dynamically as it starts up. Note if it fails to register, you can see it in the log in the first page or so as a failure.
January 27, 2011 at 10:58 am
Ferguson, thanks for the useful info 🙂
I've always manually created the spn manually using setspn.
January 27, 2011 at 11:04 am
Nils Gustav Stråbø (1/27/2011)
Ferguson, thanks for the useful info 🙂I've always manually created the spn manually using setspn.
That works, but it can cause some issues on instances with dynamic ports. Now if you always hard code those....
The nice thing about the above, if you use the same service account(s) for lots of instances and servers, is that you do it once, and all the instances and servers self register the SPN's.
The nice thing about doing it yourself is you are sure it happened. 😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply