November 21, 2014 at 8:09 am
Just remembered. Our network password sync software system. Next phase is putting the hook into it into SQL Server. So when a persons network password changes if they have a SQL Authenticated login into a SQLServer it will change their password to the same as their network password. Right now we are beginning to use it to manage our SQL Server service ID password changes.
November 21, 2014 at 8:10 am
Markus (11/21/2014)
--------------------------------------------------------------------------------
Our main system was just upgraded from SQL2000 to 2008R2 in May. There was no way to have the user change their password in SQL2000 and/or make it conform to the Network policy for passwords. I guess it is time to change how we handle user logins. Our SOX audit has never questioned the change password on first login for SQL2005 and newer. I guess I will implement that for all new IDs going forward as well.
You can also go back and implement that on the older imported IDs (if you haven't already).
The week after Thanksgiving I am going to start in our DEV environment. 🙂
November 21, 2014 at 8:19 am
spaghettidba (11/21/2014)
Brandie Tarvin (11/21/2014)
Damn. I think I just came up with another story idea.Thanks Brandie! We really needed the fantasy of a writer here 🙂
"The creepy DBA" - out soon in libraries near you 😀
I currently support 7206 production databases across 8 servers (though the bulk of them are split sort of evenly across two application servers), and 296 client facing databases across three other servers. And counting!
:w00t:
November 21, 2014 at 9:37 am
ScottPletcher (11/20/2014)
Jeff Moden (11/20/2014)
ZZartin (11/20/2014)
There's a difference between a service account login used by an application to connect, which a dba should know as the dba is likely the one who will need to configure that login and a user login.The DBA doesn't need to know the password of such accounts to maintain those same accounts.
I'll also say that such accounts should also be Windows Logins and use Windows Authentication rather than SQL SERVER Authentication, just like any other user.
Not always possible. Some applications are (still) written so as to require a native SQL login to function.
Unfortunately, that's very true. Still, the DBA doesn't need to know the password to maintain the login.
--Jeff Moden
November 21, 2014 at 1:14 pm
I just tired to have a squirrel user and one small dept. app. user test out the expire users password and it basically kills them as it won't prompt them for a password change and it simply states that their password has expired. Granted, if they used Mgt Studio it prompts just fine. However, neither have Mgt studio on their PC. :w00t:
November 22, 2014 at 4:50 pm
Markus (11/21/2014)
I just tired to have a squirrel user and one small dept. app. user test out the expire users password and it basically kills them as it won't prompt them for a password change and it simply states that their password has expired. Granted, if they used Mgt Studio it prompts just fine. However, neither have Mgt studio on their PC. :w00t:
Wouldn't that be a problem that needs to be fixed in the app? This is also a grand reason why they should be Windows Logins. Neither you nor the APP would have to go through this bit of pain.
Yep... I realize that the app "can't be changed" (although I'd be on my way to the producer of the app with a fresh bucket of pork chops for a little point-blank range personal time). How many such users do you have?
--Jeff Moden
November 24, 2014 at 5:21 am
Jeff Moden (11/22/2014)
Markus (11/21/2014)
I just tired to have a squirrel user and one small dept. app. user test out the expire users password and it basically kills them as it won't prompt them for a password change and it simply states that their password has expired. Granted, if they used Mgt Studio it prompts just fine. However, neither have Mgt studio on their PC. :w00t:Wouldn't that be a problem that needs to be fixed in the app? This is also a grand reason why they should be Windows Logins. Neither you nor the APP would have to go through this bit of pain.
Yep... I realize that the app "can't be changed" (although I'd be on my way to the producer of the app with a fresh bucket of pork chops for a little point-blank range personal time). How many such users do you have?
Almost none use Windows Auth. and we have a TON of small apps here.
November 24, 2014 at 5:22 pm
Markus (11/24/2014)
Jeff Moden (11/22/2014)
Markus (11/21/2014)
I just tired to have a squirrel user and one small dept. app. user test out the expire users password and it basically kills them as it won't prompt them for a password change and it simply states that their password has expired. Granted, if they used Mgt Studio it prompts just fine. However, neither have Mgt studio on their PC. :w00t:Wouldn't that be a problem that needs to be fixed in the app? This is also a grand reason why they should be Windows Logins. Neither you nor the APP would have to go through this bit of pain.
Yep... I realize that the app "can't be changed" (although I'd be on my way to the producer of the app with a fresh bucket of pork chops for a little point-blank range personal time). How many such users do you have?
Almost none use Windows Auth. and we have a TON of small apps here.
Hows that working out for you? And, no... no irony meant there. I'm seriously curious. It seems like that would be a huge PITA.
--Jeff Moden
November 24, 2014 at 5:46 pm
ScottPletcher (11/20/2014)
No, DBAs should not know and definitely not store end user passwords, period.App passwords are a different matter, and it can depend on the app. But stored passwords should always be encrypted, and the decrypt key known by as few people possible, period. In a true emergency, one of those people can make it available to others as needed, then the password changed after the emergency has passed.
+1000
November 24, 2014 at 5:55 pm
Brandie Tarvin (11/21/2014)
sqldriver (11/21/2014)
Every application I support uses SQL logins, and they all require at minimum the dbcreator role assigned.Yikes! What kind of application spawns its own databases? And how many databases does that mean you end up maintaining?
Agree!!
I have dealt with this in the past. Any app that required this needed its OWN instance, since having dbcreator puts other apps at risk.
Amazing how common this is among third party apps
November 25, 2014 at 6:59 am
Just to pile on, DBAs should have no more knowledge of SQLServer-related passwords (user or otherwise) than a Windows Admin should have of the domain they administer - which is to say no more than absolutely necessary to do their job.
A decent - to say nothing of good - domain admin shouldn't and doesn't need to know user's passwords and it's no different with SQL Server. I function as both Windows Domain admin and SQL Server admin and I haven't got a clue what my users' passwords are. I do know passwords for the service and proxy accounts used by SQL Server but that's a function of being a Windows Domain admin rather than SQL Server admin. Were I "merely" an SQL Admin I wouldn't expect to know those.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
November 25, 2014 at 7:06 am
You guys must live in a perfect world of applications. We have well over 100 purchased apps and virtually none of them use Windows Auth. The user interface does not allow for the user to change their password in any of them. For our Help Desk they have a hook into it for our network password tool to sync the password and change it in coordination with their network password. However, the bulk of the SQL Server based apps are small companies and don't have the resources or the desire to use Network authentication or use passsword sync toolsets. We are working with another password change tool to force a password change downstream to SQL Server in the near future.
November 25, 2014 at 7:14 am
Markus (11/25/2014)
You guys must live in a perfect world of applications. We have well over 100 purchased apps and virtually none of them use Windows Auth. The user interface does not allow for the user to change their password in any of them. For our Help Desk they have a hook into it for our network password tool to sync the password and change it in coordination with their network password. However, the bulk of the SQL Server based apps are small companies and don't have the resources or the desire to use Network authentication or use passsword sync toolsets. We are working with another password change tool to force a password change downstream to SQL Server in the near future.
Same thing here, Markus, don't worry. I feel your pain.
Even worse, most applications work with a single SQL Server login hardcoded in the sources or in plain text in some config file. All the users share the same login and I have to rely on host name or application logs to understand who's who.
I even have some applications that were installed before I arrived here and use a login with password = login name. And there's no way I can change it because the vendor is out of business and the password is in the sources.
Unfortunately, the real world is far more complex than best practices pretend it is.
-- Gianluca Sartori
November 25, 2014 at 7:19 am
arnipetursson (11/24/2014)
Brandie Tarvin (11/21/2014)
sqldriver (11/21/2014)
Every application I support uses SQL logins, and they all require at minimum the dbcreator role assigned.Yikes! What kind of application spawns its own databases? And how many databases does that mean you end up maintaining?
Agree!!
I have dealt with this in the past. Any app that required this needed its OWN instance, since having dbcreator puts other apps at risk.
Amazing how common this is among third party apps
/cry My favorite was an app we got and after creating the DB with the right collation setting up the user properly etc... the app still isn't working.
Go back to the company and after a few days of working with their support group, oh it needs db_owner on the master db. Question why the <choose your expletive>? Response, it just does.
Fine we give it that right reluctantly, oh it's still not working, another few days of working with their tech support and it comes back as oh yeah and your server collation has to be their weird choice of collation which forced us to give it its own instance which was probably a good thing anyways but very annoying since we didn't want two instances running on our production server.
November 25, 2014 at 9:04 am
100% agree: No user should know any other user's account. Ideally, when I have to reset or set up a user's password (certainly with developers), which is very rare (and usually is in AD after a password expired, but the same goes for SQL accounts), I prefer to either share a keyboard with them, or share a terminal server session, and I'll set up the password reset, they type in their password, and I complete it. That way I don't know they password for any fraction of time, however small.
Application passwords, well, that's a much more debateable area. The main rule for me here is "long, random, complex". Any good application can handle a 126 character fully random password with lower, upper, number, symbol, and extended ASCII. Most applications can handle a 40 character fully random password with lower, upper, and numbers. Sometimes you have to work down from there; but you'll never have to tell an auditor you used a weak password when the app is physically capable of handling a stronger one.
The developers absolutely can determine the app password; they have all the tools required UNLESS it's encrypted with a random key they don't (in reality, not on paper) have access to, AND they have no access to production (even to debug production problems). That's a very high bar for a business to meet.
To protect SQL Auth passwords or SQL encryption keys/passwords properly, they must be properly protected at rest at both ends, AND properly protected in motion. SSL Connection security helps with the "in motion" path, the application and users/developers must be responsible for the "at rest" application end (good luck), and SQL Server does a really bad job at the "at rest" sql server end for SQL Auth account passwords (only one iteration of salted SHA-1/SHA-512? Really? What is this, 1985?).
Note that the application encrypting the password is fraught with peril; it's really a very difficult task to do right (following encryption examples on the Internet usually leads to failure to properly encrypt, even when it looks random and works properly).
Markus (11/25/2014)
You guys must live in a perfect world of applications. We have well over 100 purchased apps and virtually none of them use Windows Auth.
Most of which start out with "what's the SA" password, so we slowly go up the chain of (more or less)
1) Windows Auth, limited account
1a) You want fries with that? You can at least get fries. You won't get this.
2) SQL auth, limited account, not even db_owner
2a) Big win when this happens, not uncommon
2b) The DBA's create the password, and it's created at the length and complexity limit of the app.
3) SQL auth, limited account, db_owner of only its own databases
3a) Not uncommon
3b) The DBA's create the password, and it's created at the length and complexity limit of the app.
4) SQL auth, "limited" account, db_owner of its own rights to some system instances
4a) ick
4b) The DBA's create the password, and it's created at the length and complexity limit of the app.
5) SQL auth, sysadmin account
5a) Congratulations, you get your own instance, since you've proven unable to play well with others.
5b) The DBA's create the password, and it's created at the length and complexity limit of the app.
6) Any auth, Windows admin account
6a) Congratulations, you get your own VM, since you've proven unable to play at all with others.
6b) The DBA's create the password, and it's created at the length and complexity limit of the app.
Viewing 15 posts - 31 through 45 (of 65 total)
You must be logged in to reply to this topic. Login to reply