March 12, 2025 at 2:32 pm
I have a paleontological database, currently in SQL Server 2016, active since 2008 R2. People use it via an Access app and ODBC connection to the server. Our organization started using Active Directory right about the time I first created this system, so I enthusiastically embraced the concept of domain groups. Besides the basic departments, I had the IT guys set up additional groups, like PaleoGuest, PaleoUser, PaleoKurator, PaleoAdmin and so on. When employees come and go, or change their status, I ask the IT crew to add or remove their membership in the appropriate group, and privileges in the database are entirely set up for these groups.
The app uses VBA to access ActiveDs.WinNTSystemInfo and the management system via winmgmts:\\.\root\cimv2, to collect username and group memberships. The ODBC connection is tested for on startup, and the app creates the connection if none exists yet.
Initial access to the server is through a server-level LOGIN for the entire department (NMP\pm2), then at the database level, USERS are defined as these AD groups (NMP\PaleoGuest, NMP\PaleoUser, NMP\PaleoKurator, NMP\PaleoAdmin, etc.) Database ROLES are then defined, with USERS as members of these ROLES, and the ROLES cascaded in privilege, with:
· NMP\PaleoDataGuest containing all USERS,
· NMP\PaleoDataUser containing NMP\PaleoUser, NMP\PaleoKurator and NMP\PaleoAdmin,
· NMP\PaleoDataKurator containing NMP\PaleoKurator and NMP\PaleoAdmin,
· NMP\PaleoDataAdmin containing only NMP\PaleoAdmin.
SCHEMAS are then defined to contain all views and stored procedures, and ROLES are given permissions on SCHEMAS, according to what I want people to be able to do. The cascading arrangement means that giving access to, say the ROLE NMP\PaleoDataUser, automatically grants access to not only USER PaleoUser, but also USERS PaleoKurator and PaleoAdmin. This all works very well. It may seem unnecessarily complex, given the low number of users I have (around 10), but it was partly a learning exercise for me, and partly an attempt to set up something that would be robust and not require much regular attention going forward. Again, it works great. When I create a new view or SP, I just put it in the proper schema and I do not have to concern myself with anything further in the area of security.
So much for background.
Now, however, the IT department has decided to essentially abandon the entire AD concept in favor of Entra. All my initial communication with the AD server to establish group membership suddenly does not work. I can't determine anything more than the user's login name, so it seems I am going to have to abandon this entire setup and build something new for permissions.
But it's even worse – the ODBC link to the server does not work from any machine using this new Entra mechanism. The IT department does not seem to know much about all this themselves, or at least they're not willing to talk about it much. How they managed to set it up I don't know, since they don't really seem to understand it. In any event, the entire organization is going that way, and I have nothing to say about it.
One of them told me that he thinks Entra is not accessible by any version of SQL Server prior to 2022. So I downloaded and installed SQL Server 2022 Express. That does not seem to work either, and now one of the IT guys says he thinks it doesn't work in Express. I don't know – all my reading mentions strictly SQL Server, but does not explicitly say that Express is excluded.
So my question (finally), is the Express version able to communicate with this new Entra mechanism? Or is there some way to use the existing machinery, or port the existing setup to Entra? Or any ideas, really. This is all new to me, and I don't understand much of what this Entra business will mean in the long run.
March 12, 2025 at 8:03 pm
It's a little bit complicated, but mostly because you have to configure your server to talk to Microsoft Entra. It involves a bit of configuration on the Azure side, so you'll have to either have rights on the Azure side or get someone to do it for you.
Once you've got SQL Server talking to Azure and your users authenticate, it's basically all the same. Probably the biggest issue is that your username format is a little different [User@Contoso.com] rather than the old [Contoso\User]. But once your users come in, the name maps to what you have set up in SQL Server, so you assign User@Contoso.com to a role and the permissions follow.
It's pretty similar to how you set up users if you're using AzureSQLDB. The below link should walk you through the process. I haven't had to do this myself, but at first glance, I don't see anything that makes me think this wouldn't work with Express Edition.
March 12, 2025 at 8:04 pm
It's a little bit complicated, but mostly because you have to configure your server to talk to Microsoft Entra. It involves a bit of configuration on the Azure side, so you'll have to either have rights on the Azure side or get someone to do it for you.
Once you've got SQL Server talking to Azure and your users authenticate, it's basically all the same. Probably the biggest issue is that your username format is a little different [User@Contoso.com] rather than the old [Contoso\User]. But once your users come in, the name maps to what you have set up in SQL Server, so you assign User@Contoso.com to a role and the permissions follow.
It's pretty similar to how you set up users if you're using AzureSQLDB. The below link should walk you through the process. I haven't had to do this myself, but at first glance, I don't see anything that makes me think this wouldn't work with Express Edition.
March 12, 2025 at 11:27 pm
Thank you, that looks promising. Unfortunately, I cannot do this myself - I have to rely on the IT department, and they are not exactly enamored of my database. They would be happiest if I and the database just went away, but the paleo department has thousands of man-hours in it, and will not give it up under any circumstances, so the IT guys have to deal with it. They usually do try to help, but it's not their favorite activity.
I will pass this on and see if they can get it to work, but since I neither understand it nor have access to it to experiment, I am somewhat at the mercy of their whims in this.
Still, if they have some hope that fixing this for me will make me shut up and go away, at least for a while, it might inspire them.
Thank you again,
Pete
March 12, 2025 at 11:46 pm
This was removed by the editor as SPAM
March 13, 2025 at 2:55 pm
One thing you can consider is migrating to Azure SQL DB. That will already have the integration into your Entra authentication, so you don't have to go through all the set up. You just need to have your IT group provision it for you. If you choose the DTU model, the lowest tier you can get will cost you about $5 per month. It's approximately equivalent to what you'd get with Express edition. The next tier up costs $15 per month, which primarily gets you more storage space.
https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single/
The nice thing about Azure is that they have a test/dev site where you can play around without risking anything. It's been awhile since I looked at the setup for the test/dev, and of course they change things all the time. It used to be that you'd get $50 of free usage per month in the test environment, so you'd be able to test stuff out and make sure all your stuff works in the Azure DB environment. Obviously, they want to make it as easy as possible to lock people into the Azure environment, so there are a lot of freebies in test to get you hooked. The downside, of course, is that your IT department may not want to spend time setting up a test environment for you, so you may have to learn how to set up Entra in a test environment to get good testing. As I say, they do have a lot of walkthroughs and free classes to get you going, because those translate into subscriptions for them.
March 14, 2025 at 12:37 pm
Thank you, that looks worth considering, although migrating the entire setup to Azure is much more work than I had hoped for just a (seemingly) simple authentication problem. And I don't know how much the existing Access/ODBC structure would have to be changed to accommodate Azure - I have zero experience with it to date. But I suppose I'm going to have to learn anyway, soon or later - maybe this will be the motivating impulse.
One thing you can consider is migrating to Azure SQL DB. That will already have the integration into your Entra authentication, so you don't have to go through all the set up. You just need to have your IT group provision it for you. If you choose the DTU model, the lowest tier you can get will cost you about $5 per month. It's approximately equivalent to what you'd get with Express edition. The next tier up costs $15 per month, which primarily gets you more storage space.
https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single/
The nice thing about Azure is that they have a test/dev site where you can play around without risking anything. It's been awhile since I looked at the setup for the test/dev, and of course they change things all the time. It used to be that you'd get $50 of free usage per month in the test environment, so you'd be able to test stuff out and make sure all your stuff works in the Azure DB environment. Obviously, they want to make it as easy as possible to lock people into the Azure environment, so there are a lot of freebies in test to get you hooked. The downside, of course, is that your IT department may not want to spend time setting up a test environment for you, so you may have to learn how to set up Entra in a test environment to get good testing. As I say, they do have a lot of walkthroughs and free classes to get you going, because those translate into subscriptions for them.
https://azure.microsoft.com/en-us/solutions/dev-test/%5B/quote%5D
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply