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""...
Business Apps
Business Intelligence
John Twohig's avatar
John Twohig
Iron Contributor
Jul 29, 2019

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
    Jul 29, 2019

    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