1

I just enabled remote connections on my SQL Server Express 2012 installation. Now I am a little bit worried about the server security because allowing connections to everybody sounds like a big security hole for me.

Is it possible to tell the SQL Server to disconnect if the user is trying to authenticate with a user which is not on my "allow" list? If so, I could add my monitoring user to this list and don't have to worry that my administration accounts are accessible.

Danielku15
  • 1,490
  • 1
  • 13
  • 29

3 Answers3

3

First of all, this is probably a question that should be asked in the DBA site. Anyway, you can set up the security of the server so that only certain users are allowed to login.

When you set up the server, you add Logins to the Server Level and then Users at the database level. Only the users that are setup can, obviously, use a particular database. You can place users into roles, so, for example, they will have read-only access to a database. You can control, down to the object level, who has access to what.

There is a good article on what SQL Server security is about here

Having said that, sometimes, after, you have setup your security, you need to disallow certain users to not be allowed to Logon. Perhaps you are doing some major upgrade to the database. One option in this case is to create a Logon trigger.

A Logon Trigger will fire every time a user Logs in. You could create a table of "allowed" users and, in the trigger, if they are not in the table you ROLLBACK, effectively disallowing the Logon.

Here is information about creating a Logon trigger

Michael Harmon
  • 746
  • 5
  • 10
1

You should be able to set up the db server to only accept connections from certain IP addresses, rather than to all of them. I'm unsure of the T-SQL syntax, but someone will surely chime in with the correct one.

If you really mean business, that being said, you'll want to authenticate clients using certificates that you give them. See this and the various articles it links to:

https://security.stackexchange.com/questions/14589/advantages-of-client-certificates-for-client-authentication

Community
  • 1
  • 1
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • AFAIK, there is no functionality built-in to SQL Server like this. Windows Firewall maybe could do it, but for SQLServer you would have to roll-your-own (dangerous and not for the uninitiated). – RBarryYoung Apr 25 '13 at 17:43
  • Insofar as I've set it up myself for my git server, the cert-based security occurs at the SSL level. Basically, as the client negotiates a secure connection, the server's certificate authority requests a certificate that it signed; if the client fails to deliver, the server rejects the connection altogether: it won't allow the client to go anywhere near SQL server, let alone try to log into it. – Denis de Bernardy Apr 25 '13 at 17:59
  • Nonetheless, this isn't anything having to do with T-SQL syntax. I'm not saying that it doesn't work or that the OP cannot or shouldn't use this, I am just saying that the bit above about it being in SQL Server/T-SQL is not correct AFAIK. I could be wrong, but I beleive that this kind of thing is setup on the Domain or Windows Server, not in SQL Server. – RBarryYoung Apr 25 '13 at 18:04
  • Yup yup, entirely correct. It's not at the SQL-level, which makes both your answer and Michael's better than mine if OP isn't sure of what he's doing. – Denis de Bernardy Apr 25 '13 at 18:08
  • Here is code for blocking access at the SQL Server level to IP addresses: http://www.shirmanov.com/2010/09/block-ip-addresses-to-sql-server-using.html. However, I think the OP will be better served studying Logins and Users and basic security setup in SQL Server. – Michael Harmon Apr 25 '13 at 19:12
1

Being able to connect remotely to a SQL Server instance does not mean that they can Login to it. If they are not authorized properly, they should still get kicked-off by the SQL Server Login Authentication sequence.

This is silent/invisible for "Trusted Logins" (where the authorization comes from their Windows Login/Domain Account), but it still happens.

If you look under the "Security" folder of your Server (in SSMS), you will see the list of authorized Logins to you SQL Server. By adding or removing these you can control who can actually create a session on your SQL Server.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137