I have just been given access to a new Windows Web Server 2008 R2 which has SQL Server 2008 R2 installed on it. When I open up SQL Server Management Studio and try to connect, it does so with Windows Authentication, however, it does not ask me for a password, and it successfully connects.

I am worried that (hopefully not) someone who shouldn't be accessing the server does so, and can then easily connect to SQL Server Management Studio without even being asked for a password, and then have access to the databases. For example, someone who has stolen the Windows Web Server login details. They now are logged onto the Web Server, and because the login details they used identify that user the privileges to access SQL Server with Windows Authentication (no password needed), they can then access the databases and data.

What would be the best approach here to ensure that anyone who wants access to my databases, must enter a password?

I know I can create user accounts inside SQL Server and assign users to specific databases (SQL Server Authentication), but this doesn't get around the issue of a user who shouldn't be on the server, just selecting Windows Authentication type, enter no password, and have full access to my databases.

Can someone please advice this on this?

Thanks folks.

  • This is really a very basic question and it shows that you misunderstand how SQL authentication works. Don't take this the wrong way - I know that everyone has to start learning somewhere - but normally it's not on production servers. If it's up and running now, I'd make as few changes as possible until I understood what I was doing, if I were you. The TechNet SQL Server blog is full of good information and is probably a good place to start learning. – MDMarra Nov 28 '12 at 14:43

If you're worried about securing the database against a malicious user that gains access to the box, there's not a lot you can do. If a Windows login on your server is compromised, they can just as easily put the database in single user mode and get at anything in it without needing valid credentials. This is why you'll typically see scenerios where the web tier is in the DMZ and the database tier is on the trusted side of the firewall with only specific ports open between the two.

Worry about keeping your box patched and well-secured, don't worry that if someone hacks your box and has privileged credentials, that they might be able to see your database. If they're already at that point, they have everything they need already.

Just selecting Windows Authentication type, enter no password, and have full access to my databases.

I think you might be misunderstanding how this works. It doesn't grant any Windows user the ability to access the databases, only ones that you've specifically allowed. Unless a user has been granted a logon to the SQL Server instance, a Windows user can't just get it. I'm assuming that you can because your user account has been granted access.

If want to see all of the logins (users, groups, and SQL logins) that have access, you can run select loginname from master.dbo.syslogins. You can check this list against who should be allowed access to verify that you haven't accidentally given rights where they aren't needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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