Error when connecting to SQL Server via VPN

[thecomputerguy]

Client asked me to provide a laptop for an employee to be able to access their SQL Server Database remotely. I am using OpenVPN as the connector.

Connecting to the network is fine, and browsing network resources works fine. User has full access to all resources. The network is configured as a Domain. The laptop is just configured as a standalone device in Workgroup.

I connect through the VPN and browse to the server at \\10.10.0.100 or \\server after which I am prompted for credentials. I manually type the credentials in the for user and store them. This allows me access to all of the network resources.

When attempting to run the program I get this error:



Followed by:



I took the laptop onsite and the issue did not change.

As a testing method I joined the laptop to the Domain after which the connection to the SQL Server worked fine.

It worked fine until I took the laptop offsite again and then it wasn't using the onsite Domain Controller as a DNS server (Probably because it was no longer authenticating).

I found a few articles: (https://techyaz.com/sql-server/trou...452-login-failed-login-from-untrusted-domain/) that mentioned to make sure that the SQL Server was set to 'mixed' mode for authentication in SQL Management Studio.

I logged into SQL Management Studio and 'mixed' mode was already selected.



What is the most painless way to make this work? I don't want to get into any heavy lifting on this one. I just want it to work.

I have since returned the laptop to Workgroup as a standalone station.

Admittedly I don't know a whole lot about SQL, and neither does their support department apparently.

 

[Sky-Knight]

There is no "quick" way about this.

Mixed mode means you can use Domain Logins, Local Windows Logins, or SQL only Logins... Pick one and make an account.

But here's the rub, the application is handling the login string, it determines what account is used. If you do not know how to configure the application, no amount of wizardry on your part will work.

Assuming the laptop in question is off domain and needs to remain so, and the user is using a local windows login on the laptop or Microsoft login, then the application would be easiest configured with a SQL login. And you need to make an an account in Enterprise Manager on the server, and link it to the correct database with the correct permissions.

Usually... network applications like this are configured with a common SQL login account that works for all users. A terrible security practice, but common. If the application is centrally controlled, and using a domain account, no non-domain member machine is going to be able to use the application. And changes here would mean changes to all other installs network wide.

Which may well force you into the virtual desktop corner. But you're barking up the wrong tree mucking with MSSQL yourself, you need to be working with the application's vendor support. Hopefully the app is the usual terribly configured stupidity that uses a text file somewhere to store the SQL login details, and you can go edit them manually after making an account for the user to use.

 

[thecomputerguy]

There is no "quick" way about this.

Mixed mode means you can use Domain Logins, Local Windows Logins, or SQL only Logins... Pick one and make an account.

But here's the rub, the application is handling the login string, it determines what account is used. If you do not know how to configure the application, no amount of wizardry on your part will work.

Assuming the laptop in question is off domain and needs to remain so, and the user is using a local windows login on the laptop or Microsoft login, then the application would be easiest configured with a SQL login. And you need to make an an account in Enterprise Manager on the server, and link it to the correct database with the correct permissions.

Usually... network applications like this are configured with a common SQL login account that works for all users. A terrible security practice, but common. If the application is centrally controlled, and using a domain account, no non-domain member machine is going to be able to use the application. And changes here would mean changes to all other installs network wide.

Which may well force you into the virtual desktop corner. But you're barking up the wrong tree mucking with MSSQL yourself, you need to be working with the application's vendor support. Hopefully the app is the usual terribly configured stupidity that uses a text file somewhere to store the SQL login details, and you can go edit them manually after making an account for the user to use.

Is there anywhere in SQL to just configure some sort of IDGAF setting when it comes to authentication?

I'm not worried about bad logins, there's 3 people in the whole company.

Like in windows you can just drop the everyone group in sharing permissions.

 

[Sky-Knight]

Is there anywhere in SQL to just configure some sort of IDGAF setting when it comes to authentication?

I'm not worried about bad logins, there's 3 people in the whole company.

Like in windows you can just drop the everyone group in sharing permissions.

No sir, you are not allowed to be insecure. Not anymore... not in well over a decade.

Microsoft will not allow any sort of network access to any service without a username and password. Even if you drop the everyone group into the permissions for a share, you STILL have to login to the machine to be part of "everyone".

 

[thecomputerguy]

No sir, you are not allowed to be insecure. Not anymore... not in well over a decade.

Microsoft will not allow any sort of network access to any service without a username and password. Even if you drop the everyone group into the permissions for a share, you STILL have to login to the machine to be part of "everyone".

Well they basically said, sorry we don't know, it's not our problem it's OpenVPN, setup a machine for the user to RDP into.

Now I know poking holes in the firewall is a nono, so I guess I just have the user VPN into the network then have them RDP into a local VM?

What the simplest way to do that? Just enable HyperV in the server and install a W10 workstation in there with RDP enabled?

I don't think I want to go the route of enabling RDS on this server since it's a bit older and only one person will ever be using it.

 

[YeOldeStonecat]

Set the VPN to have the DC as the one and only DNS for the VPN connection, so the laptop really finds the domain properly.

 

[thecomputerguy]

Set the VPN to have the DC as the one and only DNS for the VPN connection, so the laptop really finds the domain properly.

This is a half-assed environment that I took over from another guy, their gateway is a cheap ASUS router with VPN functionality, there are no options for that.

On top of that the server is running 2012 essentials, so I'm pretty sure I can't even use HyperV either.

 

[trevm999]

I'm pretty sure you should be able set the DNS settings for th OpenVPN connection client-side.

Also, the SQL Connection String has to be somewhere (or constructed from somewhere) where the client app reads it from, you likely just need to find that configuration and change it the SQL auth with username and password, but you should be able to get a domain joined computer to work over VPN.

 

[VISA MC]

Looks to me like you are trying to use windows authentication. Can you add a user to SQL server and login with that?

 

[Sky-Knight]

The product support is being stupid, the VPN has nothing to do with this. A non-domain member machine isn't capable of logging into the database using the same credentials as other systems. It simply needs updated.

Locating the DC is equally irrelevant, because the laptop isn't a domain machine!

Call those yahoos back and specifically ask them where in their application they configure the SQL connection string. Then configure it with an account that works. Don't know one? MAKE ONE!

 

[YeOldeStonecat]

Yeah I dunno, I never did a SQL app in a non domain or workgroup setup like that. I've done plenty of WANs (satellite offices connected to mothership via VPN tunnels), and remote worker VPNs....with SQL apps...and those were always...ALWAYS...domain logins. So naturally, proper setup/config of DNS was mandatory. OpenDNS usually makes that pretty easy (at least when under Untangle it does...esp with the Directory Connector).

To me, I'd be wanting to get this redone with proper business setup and equipment, not linksys ranger equipment and workgroup mode on the rigs.

 

[HCHTech]

I would cut my losses here and setup either a workstation VM or an MFF machine in the server room for the remote worker to RDP into. Don't forget if you setup a headless machine, you need an RDP license in addition to the Windows license to be legal.

 

[thecomputerguy]

I would cut my losses here and setup either a workstation VM or an MFF machine in the server room for the remote worker to RDP into. Don't forget if you setup a headless machine, you need an RDP license in addition to the Windows license to be legal.

I just ended up having the user VPN into the network and RDP into a utility workstation they had sitting there that is rarely used.

No need to complicate the situation any further.

 

[Sky-Knight]

I just ended up having the user VPN into the network and RDP into a utility workstation they had sitting there that is rarely used.

No need to complicate the situation any further.

Which also neatly keeps MSSQL traffic on the LAN where it belongs. That really is the best option.

 

[Markverhyden]

Well they basically said, sorry we don't know, it's not our problem it's OpenVPN, setup a machine for the user to RDP into.

This is what should have been done in the beginning. Done this many times with QB and other LAN only DB apps. Setup VPN to the remote LAN, then have local machine as a headless terminal to allow access. Hyper-V? Haven't done much Hyper-V but plenty on ESXi. You really want to have at least 6gb for RAM. 4gb and the VM can run like a dog. Especially with updates, had those take over an hour. But if the VM has enough resources it'll work fine.

 

[VISA MC]

If they have a server in place you could also setup as a remote app.