Connection to SQL Server using VBA connection string and Azure AD

[PaulBP14]

I have a SQL Server 2019 running on Amazon AWS and use Access 2021 as a front-end for an in-house application.

I connect to SQL Server every time I want to run a stored procedure using a connection string in VBA:

dbConn.Open "Driver={SQL Server};Server=SQLSERVER;Database=NorthWind;User ID=ABC;Password=abc123;"

Which works absolutely fine, the SQL Server has an entry in the hosts file on each PC so it can be found by Access/VBA.

The issue is that I don't have an on-premise Active Directory (I asssumed in the early days that I wouldn't need one thanks to Azure...)

All the users log onto their PC using AzureAD which is all great but, as I'm sure some of you will know, SQL Server doesn't play nicely with AzureAD (you cannot set AZUREAD\UserName up on SQL Server). I'm not sure it is relevant but for completeness I do have an Active Directory on Amazon AWS that SQL Server connects to but it isn't used for anything in particular, I only created one as I found that SSRS runs really slowly if it doesn't have access to an AD server...

My problem statement is, how do I connect to SQL Server without having to include the password in VBA? Historically I would have used the user's Active Directory username to authenticate but that doesn't seem to be available to me with my set up.

Thanks!

 

[bastanu]

Could you maybe have a table in your SQL server BE to hold the users and passwords and use a pass-through to retrieve it into a variable at run time whenever is needed by your dbConn.Open line?

Cheers,

 

[Minty]

I think if you use a later driver then I believe you can use Azure AD Authentication.

Using Microsoft Entra ID with the ODBC Driver - ODBC Driver for SQL Server

The Microsoft ODBC Driver for SQL Server allows ODBC applications to connect to Azure SQL Database or Azure SQL Managed Instance by authenticating with Microsoft Entra ID.

learn.microsoft.com

 

[PaulBP14]

AWS is evil. Read your contract CAREFULLY. Make sure they can't cancel your contract without proper notice because they are very woke and if they decide they don't like you, they will cancel your service. I wouldn't trust them with any critical data. You could lose it tomorrow. I would use a smaller local provider. They won't cancel you because you will be too important to them.

Hi Pat, that's worrying! Why do you say that? I do have local backups of all the databases so could spin up another server somewhere else quite quickly if needed although it would be very inconvenient. I choose AWS as I found Microsoft Azure servers impenetrable (as I do many things Microsoft) and AWS was really easy to set up.

 

[PaulBP14]

Could you maybe have a table in your SQL server BE to hold the users and passwords and use a pass-through to retrieve it into a variable at run time whenever is needed by your dbConn.Open line?

Hi Vlad, this is what I do. I call a stored procedure when opening Access that sends the Windows username as a parameter, the sp will then confirm or deny entry as appropriate. The issue I have is that I need a user name and password to execute the usp, I use the method described in my post using a SQL log in and a complex password but these details are stored in the VBA connection string, this is the bit I would like to get rid of.

 

[PaulBP14]

I think if you use a later driver then I believe you can use Azure AD Authentication.

Thanks Minty. This does look promising although it does imply it can only be used with an Azure SQL Server (which mine isn't) - although reading the guide suggests I may be able to create an Azure administrator on my Windows Server and open up the possibily. I'll do some experimenting.