2

I have a Windows 2008 R2 Domain/Forest functional level environment with an OpenVPN Access Server (v1.8.4.400) that is Active Directory integrated and working smoothly. I've been working with our DBA to streamline the permissions process and we have decided that we want to implement access to the various MS SQL Databases through AD User account and/or AD Group.

We created a test user: dbtest@domain.local and assigned the account through SQL Management Studio to the proper role using DOMAIN\dbtest.

We then connected to the OpenVPN box, authenticated successfully but when we try to connect to the SQL db we receive the following error:

UPDATED (Direct from Error Logs)

12/13/2013 08:00:02,Logon,Unknown,Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.0.160.201]
12/13/2013 08:00:02,Logon,Unknown,Error: 18452<c/> Severity: 14<c/> State: 1.
12/13/2013 08:00:02,Logon,Unknown,SSPI handshake failed with error code 0x8009030c<c/> state 14 while establishing a connection with integrated security; the connection has been cclosed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: 10.0.160.201]
12/13/2013 08:00:02,Logon,Unknown,Error: 17806<c/> Severity: 20<c/> State: 14.

Is there anyway to make this work knowing that the remote machines connecting through the VPN are not joined to the domain? The accounts they are connecting with are domain accounts.

Thanks in advance!

rws907
  • 231
  • 2
  • 8

1 Answers1

1

We have used OpenVPN-AS server successfully with SQL Server using AD authentication for several years. I suspect it may have more to do with the client system on the other end of the OpenVPN connection -- is the machine joined to the same domain the VPN server is on? If not, is it joined to a different domain, and is there a trust relationship between the two? If it's not joined to any domain you will still need to add the domain info when connecting to SSMS or whatever client or program you're attempting to connect through, even if you've created the same username on the client, as it will be trying to pass COMPUTERNAME\dbtest and not DOMAIN\dbtest by default.

Update re: comments

The OpenVPN-AS server shouldn't be modifying how the domain info is passed -- I suggest a quick Wireshark capture at either end, then examining the stream with the SQL authentication attempt. If the application uses SSL this may be more difficult but you may still be able to see the user details being passed. Also, if you have the source to the application, check that it's set to use Integrated Security=SSPI instead of userid and password in the connection string. Depending on the SQL Server version, there's also a Trusted_Connection=True command you can use in the connection string. This site is a great resource for building them.

Lastly, if the app is connecting via ODBC instead of the native .NET connector, you can create a trace log via the Data Sources (ODBC) icon in the Control Panel > Administrative Tools area.

Update 2

You may need to have your SQL Server set to mixed authentication mode. But I'd still run the Wireshark capture ASAP to look more closely at what's getting passed.

nedm
  • 5,630
  • 5
  • 32
  • 52
  • The users of the VPN are contractors that work off of their own workstations remotely and simply use AD for authenticated access into our network. It seems that when they login through OpenVPN, because it isn't native Active Directory on a Windows Platform, that some piece of information isn't being passed across the network to verify that the user requesting the MSSQL connection is a valid AD user or listed in the SQL permissions. Meaning that SQL isn't seeing the request come from DOMAIN\user but rather whatever OpenVPN is sending it. – rws907 Dec 13 '13 at 21:37
  • Again, if they're not joined to the domain, it's more likely that what is being passed is COMPUTERNAME\username than DOMAINNAME\username, rather than anything OpenVPN is doing with the traffic, that is the culprit. What kind of client software are they trying to connect to the SQL server with? Is it SMSS, a .NET application, a web-based tool, or something else? – nedm Dec 13 '13 at 21:45
  • It is a .NET application for GIS development that connects the user to imagery and maps. Is there any place to see what is being passed to SQL? Would it be logged as a failure audit on the sql server? – rws907 Dec 14 '13 at 00:34
  • Whoops, just saw your update after I posted my edits above. Will look at the details and try to provide more info. – nedm Dec 16 '13 at 18:41