January 11, 2006 at 2:25 am
In a branch office we have a stand-alone Windows 2003 server with SQL 2005 and some Windows XP clients. The server is also a DNS server but there is no domain controller on the remote site. We would like to use the Windows authentication mode.
When the connection to a domain controller is up it works fine. But if there is no connection and users' log-ons are cached the connection to SQL database can't be estabilshed.
The question is: how to use cached credentials on Window XP workstation to connect to the database when the domain controller is not awailable.
January 11, 2006 at 2:43 am
I don't think this is possible.
Ultimately, if the domain controller is not up then it can't issue your account with a security token when you logon to the local workstation. And if you don't have a security token attached to the login, you cannot connect to the SQL Server.
The locally cached credentials are just there to allow you to log on to the local machine, not the network so I don't think the security token is cached.
That's my understanding of things anyway. Maybe someone with more understanding of Windows security can say otherwise.
January 11, 2006 at 6:40 am
Basic and very simple:
SQL Server Windows Authentication works like this....
You are the client and I'm the SQL Server. Karl is a third person that you know and I know. I don't know you at all, but I trust Karl. You want to call me, so first you call Karl and tell him you want to connect to me. Karl calls me and says that you want to talk to me. Since I trust Karl, I trust him when he says you are who you are claiming to be. So, we connect. However, if Karl isn't available, then I have no way of knowing you are who you claim to be and won't connect with you.
-SQLBill
January 11, 2006 at 7:48 am
I'm over-simplifying some things, but hopefully the gist is received.
When your client communicates to SQL Server, it passes your credential information over the TDS stream (the protocol SQL Server uses to talk). SQL Server will then take that information and try to verify it. If the domain controller is not available, it has no means to verify the credentials, hence the failure.
The cached credentials on your workstation do allow you to log on. In this case the workstation has previously verified the credentials are valid. And unless your Windows administrator sets otherwise, the workstation will cache the credentials (since it knows the credentials are valid) in case it does run into a situation where it can't contact a domain controller. However, in this scenario you are logging onto the workstation "at the console." As a result, the caching takes place.
When you communicate to a server, you are not logging in "at the console." As a result, there is no caching of credentials. If a server receives a request and there isn't an existing connection in some manner, it has to validate the credentials. No domain controller means no mechanism of validation. In this case your connection to SQL Server fails. By the way, this isn't just limited to SQL Server. File/print services are similarly affected.
K. Brian Kelley
@kbriankelley
January 11, 2006 at 10:26 pm
Thank you very much for your answers.
Does anyone have expiriences with ADAM (Active Directory Application Mode)?
Can I solve the problem by setting up ADAM on remote sites?
January 12, 2006 at 4:46 am
The easiest solution to the problem would be to configure the server as a Domain Controller. However, this is a complete and utter no-no from a security viewpoint.
A DC at the site is however the best solution. The hardware needn't be above min spec if it's just for this purpose - a reasonable spec PC would do it. Then you just need a W2K3 licence. W2K will also do as long as you've not configured the domain to W2K3 only mode.
January 12, 2006 at 7:13 am
ADAM provides a directory service in a way where the computer isn't a full-blown domain controller. However, by instituting ADAM you would be instituting a security infrastructure that differed from your main domain (think of it as a second domain). In order to authenticate users in the first domain, you'd still need access to a domain controller.
K. Brian Kelley
@kbriankelley
January 12, 2006 at 7:16 am
Agreed. A $4-500 PC, especially for a smaller site, can certain server as a DC. This does require more administrative overhead for the domain admins, but that overhead may be offset by the phone calls you get when the network connection drops.
K. Brian Kelley
@kbriankelley
January 12, 2006 at 10:03 am
Did someone try to create local logins, similar as in the workgroup? The same local login on both workstation and server? I just tried, it worked.
I have the same local account with the same password on 2 computers. The account is in Administrators and has access to both SQL Servers. I logged in to one computer as this local account, used Query Analyzer, indicated Windows authentication and the second computer and was connected right away.
In this case users have to have their local cretedtials to have access to SQL Server too in addition to their domain credentials and when DC is not available login to their computers as local logins.
Regards,Yelena Varsha
January 12, 2006 at 10:29 am
This will not work with domain accounts. The SQL server is a member of a domain, so when a user DOMAIN\FREDBLOGGS tries to connect to the database, the server will look for an account on the domain, not the local security database. In other words, DOMAIN\FREDBLOGGS is not the same account as SQLSERVER1\FREDBLOGGS, and the SQL Server knows it.
Moreover, you'd have to keep passwords in synch. This will work if you're logging into your workstation with a local account, because you send the SQL Server just the login name FREDBLOGGS, which it interprets as SQLSERVER1\FREDBLOGGS if it's standalone, or DOMAIN\FREDBLOGGS if it's on a domain.
_Do_ _It_ _Properly_. Put a DC on the remote site. You've probably got an old PC knocking around of sufficient spec, and what's a Windows 2003 Server licence? £300 in the UK, YMMV, naturally.
Using local accounts, just like using SQL authentication, is a potential security hole, when you've got a perfectly good and one would hope secure domain that already has strongly authenticated the user.
January 12, 2006 at 10:51 am
_Do_ _It_ _Properly_. Put a DC on the remote site. You've probably got an old PC knocking around of sufficient spec, and what's a Windows 2003 Server licence? £300 in the UK, YMMV, naturally.
Agreed. And if you have a Windows 2000 Active Directory domain, that's not an issue. You can use the Windows Server 2003 license to install a Windows 2000 server. Also, if you install the DC in the remote site, make sure to make it a global catalog server as well. Especially if you are on a Windows 2000 Active Directory domain.
K. Brian Kelley
@kbriankelley
January 13, 2006 at 1:21 am
Heh. It's a bit rich, really, me telling folk to Do It Properly - I work for the National Health Service, where Doing It Properly far too often Costs Too Much. But if they'll let you have the money, do it properly every time. Having a local DC, even when remote DCs are available, cuts down on WAN traffic, speeds up logons and leaves more WAN bandwidth available for other thing like Email.
In this case, however, I wouldn't let lack of money trump the security issues. Local logins are not good news; they mean that users have seperate logins for different systems. This increases the risk of meatware security failures - where the users have so many different logins that they end up putting the usernames passwords on post-it notes on the side of the monitor. The other thing that happens in these situations is that you have a massive support desk workload from chumps with half a dozen passwords forgetting them when they have to change them. Sooner or later, their whines get through the management tree and next thing you know you're being told to compromise security by not requiring passwords to change every 90 days or whatever.
If a local DC is not an option, then I'd offer management:
1) Nowt. If you can't afford the DC, you can't afford the system.
2) Remote SQL Server. Put the SQL server at the site where the DC is. When the DC is not available, you cannot be authenticated so of course the database is also unavailable
As has been said, a W2K3 licence will allow you to install W2K Server, which will run on even the sort of antediluvian hardware that's sitting in a corner somewhere because it's too slow to play Quake II on but not quite old enough for anyone to have the guts to chuck it out. What do you need? 4 Gig of hard drive space, a PII processor and 1/4 Gig RAM, if it's just as a DC. As long as you don't have AD in W2K3 only mode, you're laughing. If you do, a little bit more horsepower is all you need for a W2K3 server.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply