Connecting to an SQL Server from a non-domain pc
Question
Monday, August 17, 2009 4:34 PM
Hi all,
Hopefully i am in the right place here and thanks in advance for anyone who can offer me any help!
I am new to SQL server as it is something we are integrating into my work environment as we speak, Therefore I will be getting up to speed on how to use it over the coming weeks. To help do this i want to be able to access our work database from my own pc.
Running Windows 7 i am currently able to log onto my work network with my laptop when in the office, and also remotely via a VPN connection when at home. This allows me to access shared drives, outlook email, network resources (printers etc.) and some in house systems as well. Basically i can work off my own laptop like it's a work laptop with no less fuctionality, but much keener and more up-to-date software and system etc :)
Anyhow my issue: Using my laptop i have discovered i am unable to access the SQL server we have setup remotely. Using windows authentication clearly doesn't work, as my laptop is not part of the work domain (so my administrator at work tells me), but he cannot understand why the 'SQL Server Authentication' wont work.
Using my 'domain\username' or simply 'username' and login details i am unable to connect. The error message it throws up is Cannot connect to SERVER\Database. Login Failed for user domain\username. (Microsoft SQL Server, Error: 18456)
Could anyone shed some light on this for me or let me know how i might get round this. I would really like to be able to access our SQL database from my PC! Hopefully this is possible...
Btw i am using SQL Server 2008. If you need any further information i will do my best to provide, although i am somewhat amateurish in this field right now i am willing to learn.
Appreciate any help anyone can offer!
Thanks
Wayner
EDIT: Sorry i should also note we tried to use the runas function to force the program to run as my domain id i.e. runas /user:domain\user in the shortcut setup. This throws up the command window, but wont acrtually accept my work Password as correct. If i type in my user pw for my laptop that takes me to the login screen of SQL Server Managment, so i'm no better off! Thought i should mention that. Thanks again!
All replies (9)
Tuesday, August 18, 2009 8:05 AM ✅Answered | 1 vote
Allowing Windows Authentication only is best practice from a security perspective so (depending on your Admins) you may struggle to get this through. Another option "could" be to create a local windows user on the sql server with matching credentials to your laptop account (same user/pwd) and you should be able to connect via pass through authentication. However, this is a bit of a pain to maintain what with changing passwords etc.
every day is a school day
Tuesday, August 18, 2009 11:36 AM ✅Answered | 1 vote
The local windows user option also sounds interetsing, but i am guessing that to fit our user controls here it would need password changing every month etc. which is not necessarily something i do locally (with only having one user on my laptop... i.e. me!) Am i getting the gist of that point?
Absolutely. Its not really a practical option when you have password policies as its high maintenance to keep the passwords in sync.
I think from a security point of view, its good to have the database as locked down as possible particularly as sensitive data is often stored there. Microsoft do give you the tools to access a database and dumb down the security but they are moving to a "default off" behaviour where you need to explicitly allow something which i think is correct.
Remember, even shared drives and the like have to have permissions set up on them but its often the case that people are a touch lazy when it comes to managing security on those (well, I certainly am!!).every day is a school day
Friday, March 12, 2010 2:37 AM ✅Answered | 2 votes
I don't know if you ever got an answer to this, but you should be able to access SQL by first accessing a unc share on the SQL server and entering your domain credentials then connecting to SQL using a SQL server account. You would have to hit the UNC share after each login before you would be able to hit SQL, but that is a smaller price to pay than having to RDP in.
Tuesday, August 18, 2009 2:30 AM | 1 vote
That isn't the issue. If the SQL Server is configured for Windows authentication only, then the only way to connect to it is by using your domain credentials. Since you don't have domain credentials on your laptop, you won't be able to connect. If you want to connect using a SQL Server login, then you need to change the security model for the instance to Windows and SQL Server authentication. Then you will be able to utilize a standard SQL Server login instead of your domain credentials. If that isn't possible, then a really simple work-around is to VPN into the office, then RDP into a machine where you have SQL Server tools installed. You are then logged into the domain, using your credentials, and can use the tools straight off the machine you RDP'd into.
Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
Tuesday, August 18, 2009 7:29 AM | 1 vote
Thanks for the response on this Michael. I had the remote desktop option as my plan B, and looks like it might just have become plan A! It's a real shame though, i love the idea of integrated systems, and having to remote in to another pc to use the function seems a shame to me. Also work pc's are on SQL 2005 whereas i have 2008, but i suppose that's just me being prissy :)
If i am reading your response correctly; am i correct in saying that there is a setting within the SQL Server itself that needs to be adjusted to allow SQL Server Authentication? If this is the case i can perhaps lobby our Admin to see if he can adjust this for me, but then would there be pitfalls to allowing this?
Thanks for the response.
Wayner
Tuesday, August 18, 2009 10:46 AM | 1 vote
Windows authentication is recommended. I wouldn't call it a best practice. It was strongly recommended in SQL Server 2000 and below, because Windows credentials were much more secure. In SQL Server 2005 and above, a standard SQL Server login is just as secure as a login mapped to Windows credentials.
Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
Tuesday, August 18, 2009 11:01 AM | 1 vote
In SQL Server 2005 and above, a standard SQL Server login is just as secure as a login mapped to Windows credentials.
I'm not sure i'd agree with that Michael. Although great strides have been made to improve the security of standard logins, i would still say that Windows Authentication is a more secure method. Indeed, the OPs problem stems from the fact that he can't login to SQL Server from a machine which isn't on the domain.
Our proposed solution is to, in effect, widen the surface area of his server by allowing mutliple types of login and allowing logins to occur from any machine which can "see" server (obviously they'd need to know the username and password).every day is a school day
Tuesday, August 18, 2009 11:27 AM | 1 vote
Thanks gents this is really good insight and i appreciate your views. I think i will discuss this with our admin chap and see if he would consider opening it up. I have a feeling he wont be too keen on that but i think it's worth a try... as you may have gathered i am trying hard not to have to go down the remote desktop route!
The local windows user option also sounds interetsing, but i am guessing that to fit our user controls here it would need password changing every month etc. which is not necessarily something i do locally (with only having one user on my laptop... i.e. me!) Am i getting the gist of that point?
Seems like a strangely tough measure to log into an SQL server given the other rights of access i can have from my non domanified laptop already. I can operate everything else within the company that we use, including our own internal systems, i just can't get into our database. Just seems if i am able to bypass Win authorising to use shared drives etc there should be no real reason why i couldn't use another shared network resource, i.e. our SQL server. Still i guess that's why i am not the admin and still learning as i go!
Wednesday, November 16, 2016 6:27 PM
Why assign the domain credentials to a specific domain name just temporarily as you suggest (by opening and logging into a UNC share) if you can do it permanently and you won't ever bother about that anymore?
Simply create a new Windows credential in the Credential Manager and fill there your domain credentials (don't forget to fill the domain in the username) and FQDN of the database server (including the port). You can use the Windows Authentication method in SQL Server Management Studio or any other application (Visual Studio) to connect to SQL Server then.
See this Stack Exchange question or this MSSQLTips' article for details.