Connect remote SQLServer Database programmatically using VPN

Question

Hey Ya'll,

I can connect to VPN and I can access Sql Server using RDP. Now, I want to access the Sql Server programmatically.

I connected to the VPN and then have the following connection string set up:

**constr = 'Data Source=servername;Initial Catalog=dbname;User ID=username;Password=passwd;'

error: login failed for username

PS: username and the password are the ones that we use to access the server remotely using RDP. and the sql server is windows authenticated and the permission is enabled for the given username.

I appreciate ur help.

Answer 1

Windows Authenticated security for the database only works if you are in the same domain as the sql server. The VPN doesn't change your domain (or workgroup) settings, it simply changes your IP configuration and sets up a secure channel to the VPN box.

You need to change the SQL security to use SQL Accounts, then create a normal SQL Server login that you pass in your query string.

Answer 2

When you "RDP" to the server, you're actually logging into the windows desktop. I presume from there you run Management Studio and connect to the "local" server using windows authentication (BTW: this whole setup is less than ideal. You don't want to force your database server to worry about running a desktop environment and management studio, too).

The "User ID" and "Password" fields in the connection string are used for Sql Authentication, which is a different thing from the windows authentication you were using. To make this work, you either need to log in to your machine with a domain account that has access to the server and change your connection string to use trusted authentication or set up an account on the server you can use with sql authentication.