MSOLEDBSQL Connection String for SQL Server

%3CLINGO-SUB%20id%3D%22lingo-sub-768820%22%20slang%3D%22en-US%22%3EMSOLEDBSQL%20Connection%20String%20for%20SQL%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-768820%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20to%20create%20a%20connection%20from%20Excel%20Desktop%20Office%20365%20subscription%20in%20a%20macro.%3C%2FP%3E%3CP%3EI%20have%20the%20following%20code%20in%20the%20VBA%3C%2FP%3E%3CP%3EPType%20%3D%20%22MSOLEDBSQL%22%3CBR%20%2F%3EHostname%20%3D%20%22tcp%3Amyserver.database.windows.net%22%22%3CBR%20%2F%3EInitCatalog%20%3D%20%22TEST%22%3CBR%20%2F%3EUserName%20%3D%20%22xx%40myserver%22%3CBR%20%2F%3EPasswd%20%3D%20%22bigpassword01%22%3CBR%20%2F%3ETrusted%20%3D%20%22yes%22%3CBR%20%2F%3E%3CBR%20%2F%3EsConnectionString%20%3D%20%22Provider%20%3D%20%22%20%26amp%3B%20PType%20%26amp%3B%20%22%3B%20%22%20_%3CBR%20%2F%3E%26amp%3B%20%22Server%20%3D%20%22%20%26amp%3B%20Hostname%20%26amp%3B%20%22%3B%20%22%20_%3CBR%20%2F%3E%26amp%3B%20%22Initial%20Catalog%20%3D%20%22%20%26amp%3B%20InitCatalog%20%26amp%3B%20%22%3B%20%22%20_%3CBR%20%2F%3E%26amp%3B%20%22User%20ID%20%3D%20%22%20%26amp%3B%20UserName%20%26amp%3B%20%22%3B%20%22%20_%3CBR%20%2F%3E%26amp%3B%20%22Password%20%3D%20%22%20%26amp%3B%20Passwd%20%26amp%3B%20%22%3B%20%22%20_%3CBR%20%2F%3E%26amp%3B%20%22Trusted_Connection%20%3D%20%22%20%26amp%3B%20Trusted%20%26amp%3B%20%22%3B%20%22%3CBR%20%2F%3E'%26amp%3B%20Encrypt%20%3D%20%22%20%26amp%3B%20Enc%20%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20hit%20the%20connection.open%20part%20of%20my%20code%20I%20get%20an%20Invalid%20connection%20string%20attribute%20error%3C%2FP%3E%3CP%3EThis%20is%20what%20it%20looks%20like%20from%20the%20Watch%20variable%26nbsp%3B%3C%2FP%3E%3CP%3E%22Provider%20%3D%20MSOLEDBSQL%3B%20Server%20%3D%20tcp%3Amyserver.database.windows.net%3B%20Initial%20Catalog%20%3D%20TEST%3B%20User%20ID%20%3D%20xx%40myserver%3B%20Password%20%3D%20bigpassword01%3B%20Trusted_Connection%20%3D%20yes%3B%20%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20aren't%20many%20examples%20of%20people%20using%20the%20MSOLEDB%20connection%20string%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20bit's%20missing%20or%20is%20an%20invalid%20attribute%20VBA%20is%20terrible%20about%20helping%20out%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-768820%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBusiness%20Apps%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBusiness%20Intelligence%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778079%22%20slang%3D%22en-US%22%3ERe%3A%20MSOLEDBSQL%20Connection%20String%20for%20SQL%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F292632%22%20target%3D%22_blank%22%3E%40turagittech%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20almost%20always%20use%20Power%20Query%20in%20Excel%20now%20so%20it%20has%20been%20a%20while%20since%20I%20have%20done%20this%20but%20I%20don't%20think%20you%20can%20provide%20a%20username%20and%20password%20and%20also%20have%20Trusted_Connection%20%3D%20Yes%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778272%22%20slang%3D%22en-US%22%3ERe%3A%20MSOLEDBSQL%20Connection%20String%20for%20SQL%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778272%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F4770%22%20target%3D%22_blank%22%3E%40John%20Twohig%3C%2FA%3E%26nbsp%3BI%20found%20the%20answer%20and%20yes%20you%20can%20do%20that%20as%20well%20as%20their%20is%20a%20constuct%20you%20need%2C%20it%20turned%20out%20a%20character%20in%20the%20password%20was%20throwing%20us%20off.%20After%20a%20bit%20of%20testing%20we%20found%20suitable%20characters.%3C%2FP%3E%3CP%3EThis%20format%20will%20work%20for%20VBA%20withe%20the%20MSOLEDBSQL%20driver%2Fprovider%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%3EsConnectionString%20%3D%20%22Provider%20%3D%20%22%20%26amp%3B%20PType%20%26amp%3B%20%22%3B%20%22%20_%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26amp%3B%20%22Data%20Source%20%3D%20%22%20%26amp%3B%20Hostname%20%26amp%3B%20%22%3B%20%22%20_%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26amp%3B%20%22Initial%20Catalog%20%3D%20%22%20%26amp%3B%20InitCatalog%20%26amp%3B%20%22%3B%20%22%20_%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26amp%3B%20%22User%20ID%20%3D%20%22%20%26amp%3B%20UserName%20%26amp%3B%20%22%3B%20%22%20_%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26amp%3B%20%22Password%20%3D%20%22%20%26amp%3B%20Passwd%20%26amp%3B%20%22%3B%20%22%20_%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26amp%3B%20%22Trusted_Connection%20%3D%20%22%20%26amp%3B%20Trusted%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20will%20leave%20others%20to%20fill%20in%20the%20rest%2C%20but%20a%20reminder%26nbsp%3Bto%20check%26nbsp%3Bout%20the%20strong%20passwords%20rules%20and%20the%20disallowed%20characters%20for%20connection%20strings.%20I%20did%20a%20blog%20post%20with%20all%20the%20details%20if%20someone%20needs%20additional%20information%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fblog.pameacs.com%2F2019%2F07%2Fconnection-strings-for-vba-and-msoledb.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fblog.pameacs.com%2F2019%2F07%2Fconnection-strings-for-vba-and-msoledb.html%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

2 Replies
Highlighted

@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

Highlighted

@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