Through a series of events too long to go into here, last year I was involved in helping to move a test SQL Server 2019 database from on-premises, to a VM in Azure. However, after it was migrated, they no longer needed me and that was the last I heard of or knew about it, until today.
Today I learned that the application (a third-party web app) that uses this database encountered user account authentication issues, so they decided to leave the database on-prem. I am disappointed to hear this as I thought it would be an excellent use of SQL Server in Azure. Because they no longer needed me after the initial push to get the database into the VM in Azure, I wasn't involved in the discussions as to what the problems were, etc. This, for me, is all after the fact. But I'd like to know, is it harder to do AD authentication against a SQL Server 2019 database in a VM, in the cloud? If so, why? I realize that there's nothing I can do about it now, but theoretically speaking what might be done in order to make it possible to enable user account authentication against a SQL database in a VM in the cloud?
Kindest Regards, Rod Connect with me on LinkedIn.
So I can't answer for sure, but I've looked at enabling AD authentication from Azure and it, at least with what I have available, seems to be a bit "finicky." I'm presuming the organization wanted to use their on-prem AD for authentication, rather than standing up an Azure AD, as well.
Where it seems it gets finicky is, you need to make changes to the local AD to "talk" to the Azure AD. I did some quick searching to try to find some of the details, and found this:
https://learn.microsoft.com/en-us/azure/architecture/reference-architectures/identity/
So it's not as simple as "point, click, done," there's some effort involved on both ends of the pipe. Could be they started down the path of doing this, decided it wasn't worth the effort and called it quits.
August 1, 2023 at 5:40 pm
It is exactly the same as any regular SQL server, and has the exact same requirements as any other AD member server.
The AD sites need to be configured correctly, DNS needs to be configured correctly and there should be a domain controller in the same site as the SQL VM. You can get away without a DC in every AD site, but for critical servers I would not recommend it.
August 1, 2023 at 6:33 pm
being a VM it is the same as having it on prem from an AD configuration point of view - unless someone decided to create a new domain for that VM.
what does need to be configured is the networking side of things from Azure to Onprem - (and to other cloud services if applicable) - this includes setting up VNET, firewalls, dns rules and so on. These type of rules can be set on subscription, vnet, vm or os level - (the higher on chain it is the easier it is to manage), but it does have to be done correctly.
if the above is not setup correctly then there can be issues - but nothing directly to do with authentication, but rather with the route/hops in between.
August 2, 2023 at 1:26 pm
In a nutshell, they can do anything in the cloud (around VMs & AD I mean) that they can locally. There's no reason why it would just utterly fail to function except that they've misconfigured it.
This is one of the horrors that comes up in cloud migrations. Instead of learning the new technology, Azure != On-Premises, they approach it exactly the same way, expecting exactly the same behavior. Fact is Azure-AD (has a new name I don't have memorized yet) is different... to configure and manage, not in what it does or how it interacts with a VM. They simply haven't learned the new way they need to operate and are therefore punting back to what they know.
This extends beyond the cloud. You see people running SQL Server 2022 with the compatibility mode set as low as possible, optimizer enhancements turned off, no Query Store, trace events running everywhere, no columnstore indexes, actively avoiding every possible improvement or enhancement because those things don't work exactly the same as the thing they learned to do in 2008.
While I approve of the conservatism (not political, technical) of many DBAs when it comes to data management to ensure the lights stay on, some take it far too far. They don't bother learning enough about how the new thing, whatever that is, works in order to intelligently choose not to use it. They simply look on it as new, and therefore evil, wrong, bad, not the way we do things around here. Further, the truly lazy get to use it as an excuse not to bother learning new things. If it worked good enough in 2008, it works good enough now, don't confuse me with your facts and logic. /<rant>
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 2, 2023 at 2:32 pm
I'd like to click each answer here as an Answer, but I can't. Thank you all for your responses! It gives me some ideas as to what was probably encountered during the attempt to get SQL 2019 to work in a VM in the cloud, then abandon the idea and retreated to on-prem. The reasons given here by you all are issues I am not experienced at, but from my experience with my fellow developers, I think it is likely true that modern technologies are involved and people don't want to learn anything new. That certainly is true with most developers I work with here. They don't want to use .NET Core, but they don't have any legitimate reasons why they shouldn't. (And they have some very unbelievable ones to stay on 10+ year old technologies.)
BTW, I don't want to imply that my coworkers are evil, bad, etc. They're fine people, loving parents, and spouses, hardworking, etc. They're just very reluctant to learn anything new.
Kindest Regards, Rod Connect with me on LinkedIn.
August 2, 2023 at 2:35 pm
Of course they're not bad people. Just people. I see it all the time. It's perfectly normal, though a bit regrettable, behavior.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 2, 2023 at 3:00 pm
Of course they're not bad people. Just people. I see it all the time. It's perfectly normal, though a bit regrettable, behavior.
Of course they're not bad people. Just people. I see it all the time. It's perfectly normal, though a bit regrettable, behavior.
I sometimes worry I might leave the wrong impression. So, I'll go out of my way to make it clear I'm not calling my coworkers bad.
Kindest Regards, Rod Connect with me on LinkedIn.
August 2, 2023 at 3:06 pm
Nah. If anything its me and the way I call out people's reluctance to embrace new stuff (that's actually functional and useful, not just new). I'm pretty sure I cross the line on that regularly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 3, 2023 at 2:16 pm
HAHAHAHA! I like the way you put it, Grant. It is functional and useful. And I recognize that, but I tend to see the new and shiny. And for some, being new and shiny doesn't carry any weight. I need to emphasize the functional and useful aspects more.
Kindest Regards, Rod Connect with me on LinkedIn.
August 3, 2023 at 4:05 pm
Grant, I thought you might be interested to know that at a meeting yesterday concerning this issue, the vendor of the product we use was also happy to hear that we're pulling the SQL Server database out of the cloud, to bring it back on-premise.
Kindest Regards, Rod Connect with me on LinkedIn.
August 3, 2023 at 4:37 pm
So odd. Oh well. This is a hard win. Has been for a decade.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 3, 2023 at 6:14 pm
I think all of this cloud stuff is a bit odd. It's actually very old thinking. Remember when people thought you were stupid if you didn't "decentralize" so that you wouldn't have a single point of failure or have to deal with "main frame people"? Now people think you're stupid if you reject the inherent centralization of the cloud even though it's been proven by many world wide outages that it actually has become a single point of failure.
Heh... and "reluctance to embrace new stuff" (even if it's a supposed good change) comes from having been burned so many times. I've not had an SP or CU break anything in SQL Server 2016 for years now. The reason, of course, is they stopped supporting it. Another way of saying that is "they stopped breaking it". 😉
We've started our migration from 2016 to 2022. We waited until at least CU3. Remember what recently happened with CU4? Are you serious? They're still breaking stuff with methods that are supposed to fix things?
There are some things in 2022 that will make life a little easier and a little faster but we were doing just fine on 2016. And that new stuff on 2022? Things like the STRING_SPLIT() run a fair bit faster but, since it took them nearly 2 decades to build it and another 7 years to finally get it right, we have some good methods to do what we need without that. And the GENERATE_SERIES() function? Same thing except they also have a hidden "break" in it that I don't believe even MS knows about (they really need to get better at testing). They're like much of the rest of the world... "get it done" is all they want to do and they don't actually understand what "done" should mean.
And all the hoop-la when 2016 was announced? I'm pretty sure that 99.9% of the world doesn't realize how they broke some date code based on the DATETIME datatype that just might be causing a lot of people some serious accuracy problems that they've never found because no one knew to check for it. I found it about a year ago quite by accident.
I've tried Extended Events. I hate it even now. My initial reluctance to try it was well founded, IMHO... not your not so humble opinion.
And then you have things like the "newer" temporal datatypes. Compared to the functionality of the old DATETIME datatype, they suck so bad they have their own field of gravity.
And how about that "great" change they made with the MAX datatypes? The datatypes themselves are bloody awesome but they didn't tell what that the supposed "improvement" of defaulting LOBs to "in row" does to the page density on your Clustered Indexes due to "Trapped Short Rows" (my name for the problem). Worse yet, the fix for that (use the table option to force them out of row) causes another "ExpAnsive" Update issue because they made the pointer "ExpAnsive" when it goes from NULL to some value.
And how about that wonderful option to do row compression, which changes most of your fixed width numeric columns to variable width, which also causes "ExpAnsive" Updates. What genius figured that page compression must first be row compressed? And then what genius didn't think of the auto-magic GZipping and unzipping of LOBs instead of making it only "programmable"?
And how about that "It just runs faster" junk they did with TF1117 and TF1118? Sure, they made it so you could override those on every database except the most important one... TEMPDB. It needs to be turned off when using IDENTITY INSERT on large tables because of another fault, which they still haven't fixed, that causes the full content of the target table to be inserted into TEMPDB for sorting during the Insert with IDENTITY INSERT on.
The list goes on but I'll quit here. I won't talk about PIVOT, FORMAT, INDEX REBUILD, INDEX REORGANIZE, or many of the other "improvements" that they made that they still haven't fixed.
The point that I'm trying to make is that "reluctance to embrace new stuff" comes from not having that reluctance (or even choice, in many cases) and getting burned by the flaming cactus we ended up having to "hug". It's not "reluctance". It's extreme caution that we had to learn because a seriously high number of changes have not actually turned out to be better.
Another thing to remember... "Old people (who are usually the most "reluctant") aren't hard of hearing... We're just tired of listening" to all the hype about new junk that either breaks, is less effective, is missing previously available functionality, or requires a whole bunch of "learning" to use properly to get the same stuff done more slowly than some of the old ways.
"Change is inevitable... change for the better is not" and a whole lot of people learned that unpopular lesson a long time ago.
And, speaking of "reluctance to embrace new stuff"... why are people so reluctant to learn the tools that work great... and have for decades? All that stuff that they don't know is actually "new stuff" to them... stop being reluctant to embrace THAT! Another way of looking at it is, since they never took the time to learn the old way correctly, why the hell do people think they'll learn the new way correctly? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply