1

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?

Improve this question

3 Answers 3

Reset to default
7

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Brilliant! This is finally the answer I was looking for. As you said, I thought I was passing my windows credentials when the server was expecting sql server ones. I ran the command and I can now connect! Thanks DonHector, you saved my life!
1

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.

Improve this answer

2 Comments

Thanks for the reply. VPN isn't an option for me. Surely there must be a way to connect to a remote sql server that's not on the same domain? B.
That's what SQL Server authentication (aka Mixed mode) is for. Is there no way to convince the DBA to give you limited access using SQL Server authentication?
0

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

Improve this answer

1 Comment

Thanks for your reply but that didn't work. From what I see, isn't not easy to connect to another domain if you're not already a member. I'll keep searched and see if another solution works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.