CODEWITHSUNDEEP
sql-servervbaexcel
Abishur
Abishur

Reputation: 189

Excel use VBA to connect to SQL using Windows Authenication

I'm working in an environment where users have two separate accounts. One logs them into their host machine, and then they use a separate one to access a VM with special software.

One of the VMs has an SQL database that records alarms that they would like to be able to access from excel installed on the host machine. I've tested the following code out and it works fine on my test VM, but it fails from the host VM.

Sub Alarm_Query()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


With cn
    .ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=SERVER;INITIAL CATALOG=A2ALMDB;INTEGRATED SECURITY=sspi;"
    .Open
End With

I know that the reason is because the user name being used to log into the host machine does not (and should not) have the credentials required to access the SQL database. I've tried changing it to

Sub Alarm_Query()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


With cn
    .ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=SERVER;INITIAL CATALOG=A2ALMDB;"
    .Properties("Prompt") = 2
    .Open
End With

Which does let me enter a unique username/password but when I give it the correct domain account I get an error message saying

"Login Failed for user.... Reason:Attempting to use an NT account name with SQL Server Authentication

. Is there a way to prompt the user running my macro to provide a username/password using windows authentication separate from what they are logged in as?

If not it seems like my options are to

1) Set it up for them to run it from within the VMs (Possible, but inconvenient)

2) Hard code an SQL username/password with read-only access to the database (Don't like the idea of this at all)

3) Add the host's username on an individual basis to have access to the SQL database (not really feasible)

4) Add the entire user group for the host's usernames to have read-only access to the SQL database (tolerable, but opening up access to a much wider base than I'd prefer)

Upvotes: 2

Views: 9094

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

Windows Authentication will not let you enter a user+password - that's the whole point: Windows does the authentication. If you want a user+password, you need to set up an SQL user.

Make sure whatever user is logged in on your host VM has access to the SQL instance, and it should just work.

Unrelated side note, this:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Defeats the purpose of this:

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

If you want late-binding, you should declare cn and rs as Object variables. Otherwise there's no point using CreateObject - just New it up, the project necessarily has a reference to ADODB if these declarations compile!

Upvotes: 5

Related Questions