June 29, 2007 at 2:01 pm
Due to SOX, we have need to use NT authenication for users that does not sign onto our domain. They will be connecting through VPN. How do we go about handling this?
July 3, 2007 at 8:18 am
Set up a trust between the two domains? let them remote into a machine on your domain and access it from there? Either possible?
July 3, 2007 at 10:10 am
If they are not coming from trusted clients, you can't without duplicating user accounts on the client machine and the SQL Server machine. Creating a domain user account won't do.
As Rich indicated, if they are coming from another domain environment, a trust relationship could be set up where your domain trusts theirs, but there are security implications to that greater than just getting SQL Server online.
Can you describe a bit more about the situation? VPN, but is it site-to-site? End users connecting from home?
K. Brian Kelley
@kbriankelley
July 5, 2007 at 5:23 am
These are people outside our company.
July 5, 2007 at 6:58 am
Then there probably is no trust relationship... meaning that there isn't a way to do this without jumping through a whole lot of hoops. You'll probably want to verify with your domain admins/directory administrators. If you're being asked to provide access using Windows authentication, it is possible that other resources need to be accessed in a similar manner. Your admins may have already made allowances. In that case, if they've done something like established a trust relationship, etc., it's doable. But if nothing like that has been done, you aren't going to be able to use Windows authentication.
K. Brian Kelley
@kbriankelley
July 27, 2007 at 5:36 pm
Another approach would be to use 2 factor authentication for SQL Server - the logins would be database logins but hard tokens could be used to secure the logins and ensure the identity of the user is known.
Thanks
Herve
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
July 30, 2007 at 2:24 pm
I certainly do not want to contradict Brian (who can run circles around anyone else here on security), and I do not have the details, since it was our network guys that set it up, but I believe it can be done without too much trouble.
A client I worked at setup a similar scenario, where outside people (partners) accessed a web-based VPN server (NetScalar if I remember). They would log in the VPN using an account setup in our domain; this account was in the domain, but in a different OU than rest of "normal" users and had only access to the one server. When they accessed our application server, we saw them as the Windows account they used at the VPN logon, not any account or domain that they were in at the home network.
Now, this access did not go to SQL Server. We would authenticate the user at the web site (using .Net sites), then the web site application would access the database using its own account (I rarely ever allow user accounts to access databases directly, only allow application specific accounts access to database connections). But since the web site used Windows authentication, and successfully authenticated these VPN users, I would imagine the VPN account could also access SQL Server if permitted.
Again, I do not know how to setup the Active Directory and/or VPN to do it, but I do believe it is possible and not very difficult to do.
Hope this helps
Mark
July 30, 2007 at 4:16 pm
Okay, this is a different scenario. The web application is connecting via a service account, so Windows authentication is being used as far as the connection between those two tiers. We do this a lot, too, but not just to ensure Windows auth. It also allows OLEDB Resource Pooling.
However, if you want to go directly from client system to database server, that's where you're going to break down. Windows auth is possible in those cases but it means a user account (which the user is running the client connecting to the database) must exist with the same username and password on both the client system and the SQL Server computer (not the domain, but locally). In this case SQL Server is able to authenticate using NTLM, but keeping the passwords in synch is a nightmare.
K. Brian Kelley
@kbriankelley
July 30, 2007 at 6:02 pm
>However, if you want to go directly from client system to database server, that's where you're going to break down.
Brian: But how is it different with a web server or SQL Servere authenticating a client using Windows Authentication. The client is given a Windows account from your domain from the VPN server. The client then has 2 (or more) Windows accounts. When a web server or SQL Server asks for authentication, the client will offer each of its user accounts until one works.
Does not SQL Server work like Windows server and like IIS, where it has Windows networking doing the domain account authentication and password checking for all Windows authentication (file server, IIS or SQL Server), then pass the requesting server the message that says "Yeah, this dude's all right be me"? At that point SQL Server (or IIS or .Net or whatever) then checks if that authenticated Windows domain account is valid for its service.
Mark
July 30, 2007 at 10:24 pm
Not exactly. IIS is designed to take credentials, such as through basic auth and validate that to a Windows user account. SQL Server is not. You see issues like this when the web server and SQL Server aren't in the same domain (and aren't in a situation to take advantage of a trust relationship. An example KB article which details a workaround is:
Authentication methods for connections to SQL Server in Active Server Pages (247931)
Basically, when a client that's not in the same domain (or connecting with a user account from a trusted domain) tries to connect via Windows authentication, you have the same issue. If the user account logged on to the client matches a local user account on the server where SQL Server runs, then NTLM authentication can happen and all is well. However, if SQL Server doesn't recognize the domain of the incoming set of credentials, it's not going to pass them on to validate against the domain for which it is a part of.
K. Brian Kelley
@kbriankelley
July 31, 2007 at 8:10 am
Thanks, Brian, for clearing up the differences. Makes sense now.
Mark
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply