October 24, 2002 at 9:51 am
When a person using a Windows account logs into SQL Server how does the authentication process work?
Does SQL Server keep a copy of the password in SQL Server? Does an authentication request go to the Primary Domain Controller each time the person connects to SQL Server? or is that not needed when the person is already logged onto their workstation with that Windows account?
BOL says it uses a trusted connection. Am I right in understanding this to mean that SQL Server simply checks to see if the Windows account logged into the workstation matches a Windows account in SQL Server or is there more to it than that?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 24, 2002 at 10:08 am
Actually more complicated, but basically when the account logs in, it gets a token with the SIDs that the user has associated to them (user + groups). These are then compared in SQL Server with the SIDs in the login process.
Steve Jones
October 24, 2002 at 10:17 am
After the initial login to the workstation, does any authentication go to the PDC when connecting to SQL Server or does it all stay local?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 24, 2002 at 9:47 pm
It is not supposed to make another trip to the Domain Controller (PDC or BDC). The security token has enough detail to provide SQL the answer as to who the user is and this is why it is considered a trusted connection.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 27, 2002 at 12:59 pm
For a reference on the security architecture http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/Default.asp
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 29, 2002 at 1:33 pm
I have trouble to connect to the SQL Server with windows authentication settings from ASP or VB.
It works if I use SQL Server authentication as: dsn = "Provider=SQLOLEDB; Server=xxxxx; Database=xxxxx; User Id=sa; Password=;"
How can I change DSN parameters if I use windows authentication?
October 29, 2002 at 4:00 pm
In BOL search for "SSPI".
I ain't programmer. My developers had problem with NT authentication. Later we found the magic word.
December 24, 2002 at 8:27 am
First post. Like the site. Very useful!
I have a problem, it takes users using Windows authentication a lot longer to get up and running than users using SQL Server authentication. Specificially they access the database from an Excel application using ODBC. Any idea as to why Windows authentication should take so much longer?
Thanks for any help, and Merry Christmas!
December 24, 2002 at 9:04 am
Shouldnt take much longer. Might want to talk to your network admin, see where the nearest domain controller is and work to reduce the delay. I doubt the issue is SQL directly.
Andy
December 24, 2002 at 9:22 am
If you run an NBTSTAT -a on the SQL Server, you can figure out where it's resolving. Here are the NetBIOS codes:
http://support.microsoft.com/default.aspx?scid=kb;en-us;163409
We've had some problems where our servers are resolving to BDCs that are across the WAN instead of the one that's local. That's not supposed to happen, but it is. We've had to hardcode DCs in LMHOSTS in some cases. It's probably a combination of server and network configuration that's causing our problem, as it only started recently and we've made some changes.
In any case, you can see the DC list and make sure that SQL Server is getting this list properly from the WINS server or from the Computer Browser for the subnet.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 24, 2002 at 9:45 am
There is a new security book from CurlingStone, SQL Server Security Distilled, that covers this in great detail. If it's taking significantly longer, you have network issues.
As Anatares said, once authenticated, you should not be going back to the DC.
Steve Jones
December 24, 2002 at 10:15 am
Hmmm. Now I'm confused. Some of you say its communicating with the DC and others don't!
That book sounds worth a look though. I'll check it out.
You've all given me food for thought though. Thanks for the prompt replies.
December 24, 2002 at 10:15 am
Hmmm. Now I'm confused. Some of you say its communicating with the DC and others don't!
That book sounds worth a look though. I'll check it out.
You've all given me food for thought though. Thanks for the prompt replies.
December 24, 2002 at 11:28 am
What should happen:
User contacts SQL Server, sends credentials, SQL then verifies these with the DC and once verified, checks syslogins (and sysusers) for access before responding to the client.
Steve Jones
December 24, 2002 at 11:57 am
Ok that makes sense, so the delay will be in the comuunication with the DC. As you say, network problem then most likely...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply