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:
- Require developers RDP into the VM to do any development work
- Figure out how to get the domains to "trust" eachother (something I wouldn't begin to know how to tackle)
- Create individual SQL Server logins for users.
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?
Executive Junior Cowboy Developer, Esq.[/url]