Forum Discussion
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 TwohigIron Contributor
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
- turagittechCopper 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 = " & TrustedI 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