Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like my app's login dialog to give users a drop-down-list of login names on the server. So I'm wondering about the most secure way to retrieve this.

The best I can come up with is a login with a hardcoded password that can only access a stored procedure that'll return a list of other logins. What's the best way to harden this? I would love to create a login trigger that'll return what I want and then force the connection to close, or a trigger that will run for all statements, but this doesn't look possible..

Thanks!

share|improve this question
1  
Which version of SQL Server? Will it be possible for users to try and make a connection to SQL server outside of your application? For example, is something stopping your users from opening up Excel and directly connecting to the SQL server? –  Edward Dortland Dec 25 '13 at 7:39
1  
Just be aware showing a list of usernames is a security no-no. Giving away the usernames halves the necessary work to break into the system. –  Max Vernon Dec 25 '13 at 14:37
1  
This is a really bad idea - as Max said, it's a security no-no - it is more than 1/2 of the work as even if only one user has a weak password, you have provided hackers with a list to try against. If you insist on doing this, please post the site so I can be sure to not use it. –  Thronk Dec 25 '13 at 14:59
    
It's SQL Server 2008 R2. The app is a windows desktop app that accesses SQL server via an intranet. I realise it'd be more secure to not give away usernames, but I have to think about usability too. I think the usernames won't be that hard to guess, and aren't even "***"-out when being entered. –  eug Dec 26 '13 at 2:59

1 Answer 1

"Most secure" is an ambiguous statement that needs more explanation. You need to think about what kind of attack vectors you are trying to mitigate.

For example, what if a malicious user trys to brute-force your built-in account? It's going to be locked out. This creates a denial of service scenario, as normal users cannot receive a list of valid accounts either. If you were trying to mitigate a DoS scenario by using hard-to-guess logon names, you just shoot your own foot.

Hard-coding a password in your application will make it insecure. First, it can be extracted from the application, no matter how you encrypt it. That's exactly the way Blu-Ray encryption was broken. Second, should you need to change the password, you need to ship new binaries to all the users which is somewhat a hassle.

How about creating a SSIS package that publishes a list of valid logins on a network share? This moves some security configuration from the database to your AD group membership maintenance.

share|improve this answer
1  
+1 for defense in depth idea of last sentence - at least user needs to gain access to AD to get list of usernames. –  Thronk Dec 25 '13 at 15:01
    
Thanks for the answer. The usernames won't be hard to guess, and I can disable lockout against the hardcoded login-discovery account. I realise its password can be easily extracted, that's why I'm trying to lock down the login-discovery account as much as possible. –  eug Dec 26 '13 at 3:02
    
A network share is a good idea, but I fear it'll be quite burdensome to set up, this app gets distributed to customers via an install package. I don't really want to assume their AD is working properly either. –  eug Dec 26 '13 at 3:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.