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!
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!