Connection to SQL Server using VBA connection string and Azure AD (1 Viewer)

[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,

 

[Pat Hartman]

I have a SQL Server 2019 running on Amazon AWS

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.

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.

If you can't find a better solution, have them log into Access and keep their credentials there that you can use to log into the server.

 

[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.

 

[Pat Hartman]

@PaulBP14 Welcome

Woke companies regularly cancel or refuse service to people/companies they disapprove of. One that made a splash in my world because I knew people involved was when they cancelled Parlar two years ago. The news reports played down the problem and made it sound like they just refused to host Parlar's website but that was a minor problem.

When small companies decide they don't want to house/manage their own servers, they rely on large, nation wide companies like AWS if they buy into the whole Cloud business. They used to rely on local service bureaus which were companies in their community. Once AWS decided to punish Parlar for their politics (they refused to cancel the account of the then sitting President of the United States of America), they withheld access to ALL the servers that Parlar was using, NOT JUST their web page. How about their email, General Ledger, Customer files, etc. Think about it. It's like a bomb dropped on your company headquarters destroying EVERYTHING. OK, you can get it back if your backups are good enough but you are effectively out of business for the duration. Parlar was given 24 hours notice. Could you recover your entire company IT infrastructure in less than 24 hours - starting with finding a new provider?

Since the woke press approve of cancelling companies/people they disagree with, you don't get much front page news when they do it to ordinary people so they keep getting away with it.

So, if you are going to use them, make an issue of it in your contract. Make sure they can't cancel your clients with less than 2 months notice. OR, use a local company you can trust.