September 3, 2019 at 9:21 pm
Hi SSC,
My main question has to do with how best to managed SQL Server Authentication Logins, which I'll get to in just a second. First, let me give you a quick rundown of why (I think) I am forced to go this route, partially in the hopes someone will show me a better path. I should also mention I'm well versed in SQL development, but am now in the envious position of getting/having to play DBA as well (with no one directly to learn from), so talk to me like I'm an idiot (please).
We have a Azure VM rented upon which we're running SQL Server 2017. The domain it runs (lets say "vmdomain" on has no trust with the domain all my divisions machines run in (call it "corpdomain"). Currently It's just me and one other developer who are doing any sort of development in earnest, so we've been using the SA account; something I want to get rid of ASAP. The problem is, with SQL running on a different domain, I can't use Windows Authentication for any new users I want to give access to.
That leaves me with the following options as I see it:
The last one seems the most straight forward, but I also know that everything I've ever read has said if you can use Windows Authentication, do so (if for no other reason that you end up having to do IT support for things like password resets, etc.).
Assuming there are no other options I'm missing here, my understanding is you pretty much have to manually input the users password when you create the login, and something about going to each of my coworkers desks and saying "ok what do you want your password to be?" seem... wrong.
Can anyone shed light on how best I can administrate these SQL Server Auth logins in a more sane way, or even better, how I can avoid this whole Charlie Foxtrot to begin with?
September 3, 2019 at 9:52 pm
Well if you have to set up SQL Server logins you can set them up with password expiration and change password on next login and give them a default password. As you noted this means you'll now be playing help desk for everyone who forgets their password or what not.
September 4, 2019 at 1:15 am
I guess my question would be, why did you set this instance up on a different domain? That seems to be the real problem here.
Also, since you DO have a different domain, why can't you set up AD users and groups in that new domain?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2019 at 3:34 pm
There is not a simple answer here.
If this is an Azure VM, and you want to be able to use your Windows AD credentials, you are going to have to set up Azure AD, and then set up the trusts between the 2 domains. You only want a one-way trust, BTW. Like Jeff asked, why wasn't the VM set up in the corp domain? Could you be doing something ''outside the lines" and now are trying to figure it out? This is a good example of how the cloud can bite you. Spin up a new VM for development, works great, but now it's going to cost lots of money and time to actually put it into production. Not to mention the various regulations that you may well be violating, depending upon what rules your company needs to adhere to.
This should probably be something your infrastructure people need to set up and support. Have you talked to your network and security folks? How about Azure support?
SQL logins can be set up easily, but like ZZartin said, you will need to be the help desk.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 4, 2019 at 6:39 pm
Thank you for the responses, and forgive any naivitee I may have on the matter; this was someone else burning firehose from which I was asked to drink.
I guess my question would be, why did you set this instance up on a different domain? That seems to be the real problem here.
Also, since you DO have a different domain, why can't you set up AD users and groups in that new domain?
(sings Fiddler on the Roof) "Tradition!". But in all seriousness, I don't know. This seemed like an odd setup to me, but I had no background on which to draw to refute the setup.
To your second point, I suppose I could; but that requires everyone would have to RDP into that machine to do any development work, yes? Certainly not the Spanish Inquisition, but also not a streamlined development experience imo.
It sounds like trying to work through whatever security gremlins people believe to exist and fixing/aligning/whatevering the domains is the right approach (with the option of a managed instance always in my back pocket as wel.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply