November 26, 2014 at 5:51 am
Markus (11/25/2014)
You guys must live in a perfect world of applications... 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.
Does this scare anyone else besides me? I'm far from the best programmer in the world but using Windows auth is not that difficult. Smacks more of lack of desire that lack of resources.
But the guiding principle that's been hammered into my head is users at all levels only need minimum access and rights to do their job and no more.
____________
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 26, 2014 at 7:08 am
lshanahan (11/26/2014)
Markus (11/25/2014)
You guys must live in a perfect world of applications... 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.Does this scare anyone else besides me? I'm far from the best programmer in the world but using Windows auth is not that difficult. Smacks more of lack of desire that lack of resources.
But the guiding principle that's been hammered into my head is users at all levels only need minimum access and rights to do their job and no more.
A lot of applications are not coded to be MS platform specific, at least at the DB level.
November 26, 2014 at 1:46 pm
I'd actually recommend that you use windows authentication wherever possible for the following reasons:
1. You will immediately know who is accessing your data - with SQL Authentication, it's not always immediately clear who is looking at your data.
2. It eliminates any chance of you needing to know a users password.
I agree with the posts here stating you should never need to know a users password. Windows authentication stops this ever being a problem for user logins. I do store all my Application SQL Logins however.
December 1, 2014 at 11:48 am
Been on PTO for a week, so didn't catch up to this thread until now...
Regarding the app that doesn't prompt for SQL password changes, depending on how big your company is and where said company is located, this may be a SOX violation. A few years back we had similar pain with an in-house app that our Devs had to redesign (just the login piece) to add in password expirations, then methods of changing said passwords. It was hard work on their part, but now our auditors are happy.
Sometimes to get a vendor (or a developer) into gear, you need a legal stick for which to bash them across the noggin with. I suggest checking up with your company lawyer to see if there are any laws governing the security of these systems that your vendors may be violating. Given the choice between bearing the cost of a governmental lawsuit or redesiging the app login process, I'm sure they'll figure out which one is cheaper.
December 2, 2014 at 5:38 am
In our company sharing user passwords is a firing offense. As developers needing to troubleshoot user defined defects, we restore the production database to our development VM's and change the user password to Test1001 for all users. This way we can log into our copy of the production database with the same permissions as the user but we do not need to know the user's password. Handling the issue in the manner I have described keeps security intact and gives the developer community the tools to resolve issues effectively.
December 2, 2014 at 8:41 am
Jeff Moden (11/20/2014)
All bad. Really bad. The company should get a professional security audit done and then impliment just about everything they suggest (if not everything). The company would also seriously benefit by having someone come in and teach some mandatory-attendance security. And it all should be done immediately to avoid becoming something like the next Home Depot, etc.
Auditing is only valuable to a certain degree: Target, Home Depot, Michaels, et al passed a PCI audit before they were hacked. Afterwards when they were re-audited, they failed. So how much value was that audit? According to Krebs, there was one major hackee who passed an audit DURING the attack. And now the USA is implementing chip & PIN that doesn't require a PIN. I'm not really clear on how that's supposed to be more secure.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
December 2, 2014 at 9:05 am
spaghettidba (11/25/2014)
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.
Sooo, being it was designed poorly that makes the other right? I disagree with this logic.
I know in the real world it can be the opposite of what we wish, but it doesn't mean it's right. Even before I started working with databases, I managed a robust online community of hundreds-of-thousands of accounts. Even then, it's mandatory that I do not know the customers passwords or my co-workers passwords.
We even go out our way to convey that we will never ask for your password nor store passwords outside of the application. This is to ensure everyone knows that we are completely clueless to your private information unless we are using the appropriate internal secure tools to do so.
Why?
Because I do not want to be held liable if your account and information is compromised in that way. I don't care how much trust I have with a company, I do not want to be put into a position where me and my team is being audited because of some shady behavior. It's to protect you against me and me against your as harsh as that sounds.
Likewise, it's also why because the application was correctly designed to give you all the avenues you need for your password that is always encrypted and secure.
If not, I'd likely not be working there because there is only so much risk I could take that's outside of my control. But that's just me. :hehe:
December 2, 2014 at 9:07 am
eric.notheisen (12/2/2014)
In our company sharing user passwords is a firing offense. As developers needing to troubleshoot user defined defects, we restore the production database to our development VM's and change the user password to Test1001 for all users. This way we can log into our copy of the production database with the same permissions as the user but we do not need to know the user's password. Handling the issue in the manner I have described keeps security intact and gives the developer community the tools to resolve issues effectively.
Why would they need to know the production password of any user when they get to the exact same data in development using a universally known password? Btw, might this not include former employees using wireless entry? You really should change the universal password at least once a month.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 3, 2014 at 8:49 am
Brandie, good points on SOX (and, I suspect, PCI-DSS) password change requirements. Note that there may, or may not, be differing requirements for passwords that any human knows and passwords that no human knows (i.e. the app handles it 100% internally and it's not stored in cleartext).
Speaking of storing passwords in cleartext, for SQL Auth, the #1 most common way I've seen applications store this is by storing a string. There are two normal (very broad) ways to store the string:
1) As a configurable variable, be it in a .config file, a .ini file, in the registry, or some other custom configuration location.
1a) It's almost always stored in cleartext, and can be changed right there.
1b) When it's not stored in cleartext, it's almost always stored with some really horrific home-grown "encryption" that isn't much stronger than ROT13. This is often easy enough to figure out even without the source code, and then you can change the password easily.
2) As a hardcoded string in the app.
2a) It's almost always stored in cleartext, and can be changed right there (with a hex editor). Even better is when it's a whole connection string, and you can change
uid=MyUsername;pwd=MyPassword
to
uid=A6h;pwd=sò}eR‡¹Ãs8s~\áÆnz;
essentially trading username length off to increase password length. It is elegant, nice, or good? No, no, and no. Does it let you change the password in an app where you don't have the source code? Yes. Does it hide the password from anyone using a hex editor or the SysInternals Strings utility, etc., much less a packet sniffer or tools that expose RAM to the user (like HxD, my favorite free hex editor)? No.
December 3, 2014 at 12:11 pm
ScottPletcher (12/2/2014)
eric.notheisen (12/2/2014)
In our company sharing user passwords is a firing offense. As developers needing to troubleshoot user defined defects, we restore the production database to our development VM's and change the user password to Test1001 for all users. This way we can log into our copy of the production database with the same permissions as the user but we do not need to know the user's password. Handling the issue in the manner I have described keeps security intact and gives the developer community the tools to resolve issues effectively.Why would they need to know the production password of any user when they get to the exact same data in development using a universally known password? Btw, might this not include former employees using wireless entry? You really should change the universal password at least once a month.
I am so fighting with this issue myself 🙁
One of our client wants to refresh their QA from production database and then they want to change all user passwords to same. In production they have very strict access rules, but everyone can access all data in test environment.
December 3, 2014 at 12:13 pm
BTW how do you use windows login from Java EE application with application server on DMZ and database on LAN?
December 3, 2014 at 12:46 pm
Ville-Pekka Vahteala (12/3/2014)
BTW how do you use windows login from Java EE application with application server on DMZ and database on LAN?
Does this help? http://www.developertutorials.com/single-sign-on/
December 4, 2014 at 6:05 am
Ville-Pekka Vahteala (12/3/2014)
ScottPletcher (12/2/2014)
eric.notheisen (12/2/2014)
In our company sharing user passwords is a firing offense. As developers needing to troubleshoot user defined defects, we restore the production database to our development VM's and change the user password to Test1001 for all users. This way we can log into our copy of the production database with the same permissions as the user but we do not need to know the user's password. Handling the issue in the manner I have described keeps security intact and gives the developer community the tools to resolve issues effectively.Why would they need to know the production password of any user when they get to the exact same data in development using a universally known password? Btw, might this not include former employees using wireless entry? You really should change the universal password at least once a month.
I am so fighting with this issue myself 🙁
One of our client wants to refresh their QA from production database and then they want to change all user passwords to same. In production they have very strict access rules, but everyone can access all data in test environment.
We actually scrub our data when it gets restored down to Dev / Test / QC. That way the QA team, the developers, and the business analysts only see the non-PII data.
December 4, 2014 at 7:11 am
Ideally you will be using Windows authentication across the organization and the network domain admin is a different person than the DBA. However, there are scenarios, like 3rd party applications, linked servers, or service accounts, which for technical reasons may need SQL Server authentication.
I get the impression this is not commonly known, but a SQL Server authenticated user account, even a minimal user with nothing but default public permissions, can change it's own password at any point after it's created by the DBA.
For example, create a test user account.
create login tempuser with
PASSWORD = 'old%password853',
DEFAULT_DATABASE = master,
CHECK_EXPIRATION = off,
CHECK_POLICY = off;
Login as under the test account and have it change it's own password.
alter login tempuser with
PASSWORD = 'new%password853'
OLD_PASSWORD = 'old%password853';
Logout and then re-login as test account using it's new password.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 4, 2014 at 7:24 am
Eric M Russell (12/4/2014)
I get the impression this is not commonly known, but a SQL Server authenticated user account, even a minimal user with nothing but default public permissions, can change it's own password at any point after it's created by the DBA.
But that requires access to tools that allow one to write / parse T-SQL, correct? For instance, if someone doesn't have SSMS or Powershell or the SQL command line tool, the user can't make those password changes.
Yes?
Viewing 15 posts - 46 through 60 (of 65 total)
You must be logged in to reply to this topic. Login to reply