Forum Discussion

turagittech's avatar
turagittech
Copper Contributor
Jul 23, 2019

MSOLEDBSQL Connection String for SQL Server

I am working to create a connection from Excel Desktop Office 365 subscription in a macro.

I have the following code in the VBA

PType = "MSOLEDBSQL"
Hostname = "tcp:myserver.database.windows.net""
InitCatalog = "TEST"
UserName = "xx@myserver"
Passwd = "bigpassword01"
Trusted = "yes"

sConnectionString = "Provider = " & PType & "; " _
& "Server = " & Hostname & "; " _
& "Initial Catalog = " & InitCatalog & "; " _
& "User ID = " & UserName & "; " _
& "Password = " & Passwd & "; " _
& "Trusted_Connection = " & Trusted & "; "
'& Encrypt = " & Enc "

 

When I hit the connection.open part of my code I get an Invalid connection string attribute error

This is what it looks like from the Watch variable 

"Provider = MSOLEDBSQL; Server = tcp:myserver.database.windows.net; Initial Catalog = TEST; User ID = xx@myserver; Password = bigpassword01; Trusted_Connection = yes; "

 

There aren't many examples of people using the MSOLEDB connection string

 

What bit's missing or is an invalid attribute VBA is terrible about helping out?

 

Peter

  • John Twohig's avatar
    John Twohig
    Iron Contributor

    turagittech 

     

    I almost always use Power Query in Excel now so it has been a while since I have done this but I don't think you can provide a username and password and also have Trusted_Connection = Yes

    • turagittech's avatar
      turagittech
      Copper Contributor

      John Twohig I found the answer and yes you can do that as well as their is a constuct you need, it turned out a character in the password was throwing us off. After a bit of testing we found suitable characters.

      This format will work for VBA withe the MSOLEDBSQL driver/provider 

       sConnectionString = "Provider = " & PType & "; " _
                              & "Data Source = " & Hostname & "; " _
                              & "Initial Catalog = " & InitCatalog & "; " _
                             & "User ID = " & UserName & "; " _
                              & "Password = " & Passwd & "; " _
                              & "Trusted_Connection = " & Trusted

      I will leave others to fill in the rest, but a reminder to check out the strong passwords rules and the disallowed characters for connection strings. I did a blog post with all the details if someone needs additional information http://blog.pameacs.com/2019/07/connection-strings-for-vba-and-msoledb.html

Resources