I need to be able to log on to a test database from home, I created a VPN from home to the office but SQL wouldn't let me connect. I assumed it was an issue with the VPN so the next morning I took the laptop in and hard wired it to the network, desktop (domain) PC works fine but laptop (non domain) gives me this error :
Just to test I nicked a colleagues domain PC, all worked fine, nicked his non-domain laptop, same error...
I have a sneaky feeling its to do with authentication, maybe to do with pipes but I don't want to start playing and break anything... Any idea how I can get around this??
Oh SQL server is a domain member and its configureed to use SQL Authentication not windows authentication...
I've had several problems in the past with authentication issues for non network devices and got around it by using host files, for example I used host files for machines on a different network to connect thru to a SQL server running the telephony system, a host file on the server and one on the local machine sorted this, add the IP your are giving from the VPN to the SQL Server host file.
You could try changing the test instance to shared mode, and also you can store SQL Server account details locally for testing only, although not recommended.
You may want to check with your security guys to see is the SQL Server port is allowed thru the VPN on the firewall and check its using the correct port.
Yeah, its not a problem with not physically seeing the server on the network, it looks more like SQL is ignoring the laptop because its not a domain member....? How wold I put the instance in shared mode?
SQL Server instance - Properties - Security - (tick) SQL Server and Windows Authentication Mode.
What port is SQL Server listening to on the laptop, is browser enabled on the server, if your laptop is not a domain member you can authenticate by putting your VPN assigned IP into the SQL Server host file and by putting SQL Server name and IP into a host file on your laptop, although the VPN you should not need to do this.
Also there could be firewall ports blocked depending on what port might be stealing the SQL Server port, typically TCP 1433 and UDP port 1434, so more ports may need opening on the firewall or allowed thru the VPN.
SQL Server and Windows Authentication Mode was already ticked.
I created an entry in the SQL Server host file for the laptop and visa versa.
I'm not currently connected via VPN its currently hardwired into the same switch...
such a simple one I'm rather miffed, a colleague had a look and spotted it in about 2 mins...