CODEWITHSUNDEEP
sqlsql-servervbscriptconnection-stringadodb
bearaman
bearaman

Reputation: 1091

Unable to use vbscript to connect to sql server on different domain

I'm using vbscript to try to connect to a sql server on another domain. The server is part of a cluster and is SQL Server Data Center Edition (64bit). I can ping the server from my machine. I'm using the IP address to connect which works fine on SSMS. The vbscript code I'm using is:

Set cn = CreateObject("ADODB.Connection")
cn.Open "DRIVER={SQL Server};Server=123.456.345.567\instance;Database=MyDB;User
ID=domain\myuserid;Password=mypass;"
strCommandText = "Select * from mytable"
cn.Execute strCommandText

When I run this, I get the following error: Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'domain\myuserid'.

I can't join my test machine to the domain and I can't use sql authentication as it's disabled on the sql server.

What do I need to do to get this to work?

Upvotes: 1

Views: 17292

Answers (3)

donhector
donhector

Reputation: 915

Looking at you query string I see you are specifying username and password, and that means you are providing SQL Authentication credentials. However in your question you state SQL Authentication is disabled in the SQL server.

That's the reason why your SQL server is not letting you in. You are providing SQL authentication credentials when your server is not expecting them.

If SQL Authentication is disabled, then is very likely your server is not in Mixed Mode Authentication either so that pretty much means the SQL server is using Windows Authentication for logins. This configuration is beyond your control, it's your DBA who made that choice.

Therefore, if the SQL server you are connecting to is using Windows Authentication mode, your query string should be:

cn.Open "DRIVER={SQL Server};Server=123.456.345.567\instance;Database=MyDB;Trusted_Connection=yes;"

However here comes the tricky part... if you are running your VBScript from your test machine that is part of [DOMAIN_A], it will run with under a [DOMAIN_A] windows credentials. However your SQL server is expecting windows credentials from the domain he is joined into. Let's call it [Domain_B].

Unless you AD admins have created trust relationship between [DOMAIN_A] and [DOMAIN_B] your login attempts will still fail, despite now having a correct query string.

How do you solve this cross-domain issue if you can´t join your test machine to [DOMAIN_B]? Easy. Use the "Runas" command.

On your test machine joined to [DOMAIN_A], open a command prompt and execute:

Runas /noprofile /netonly user:[DOMAIN_B]\myuserid %comspec%

It will ask you for the password. Enter the "mypass" password you originally had in your query string. "myuserid" is also the username in your original query string.

After doing that, you will have a new command prompt window, but if you notice in its title, it is now running under your [DOMAIN_B] credentials.

Despite still being on your test machine joined to [DOMAIN_A], anything that you execute on this new command prompt will do it under your [DOMAIN_B] credentials. Exactly what your SQL server is expecting.

So at this point, only thing left is to run your VBScript on this new command prompt window. That should provide the appropriate credentials to SQL server.

Let us know if that solved the issue.

Upvotes: 6

coge.soft
coge.soft

Reputation: 1674

To my knowledge, there isn't an option to connect without joining the domain or using SQL Authentication. You might have luck connecting through a VPN in which you virtually join a domain.

Upvotes: 1

Farfarak
Farfarak

Reputation: 1517

Try changing your connection string, specifically driver to provider:

Give a try this one

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;

Here are conenction string examples: http://www.connectionstrings.com/sql-server-2005#sql-server-native-client-10-0-oledb-provider

Upvotes: 0

Related Questions