0

The sa account was recently locked out on my company's public-facing database due to numerous failed login attempts from an unknown user. I have their IP address in the logs but it scared the hell out of me.

I changed all my passwords and am in the process of encrypting all the sensitive data.

How do I restrict a SQL Server Authentication user to only be able to execute certain stored procedures, but do nothing else (not see anything nor even be able to do Select * From [SomeTable])?

Update:

I ended up setting up a whitelist of IPs for the firewall, creating random 90 character passwords, setting db_denydatawriter and db_denydatareader and granting Execute for specific stored procedures for specific users.

Greg
  • 8,574
  • 21
  • 67
  • 109
  • 5
    Probably off topic, but you know it's generally considered a ***really*** bad idea to leave a database open to the public internet, right? Secured or not. – Chris Shain Mar 27 '12 at 20:40
  • its not off topic. It is dead-on. Talk to your network admin. simply said, unless its inside your network on a vpn or its inside your network, no direct connection to sql-server. Also, 'sa' should be disabled on every sql server. – jerry Mar 27 '12 at 20:50

2 Answers2

3

Attacks against an unprotected sa occur all the time, is part of the arsenal of every botnet and malware scan kit out there.

The only restrictions you have at your disposal are permissions and grants. There is no mechanism to restrict what is being executed from a login (there is no mechanism similar to, say, how a ssh tunnel can be restricted to only certain commands). So it really boils down to permissions and strong passwords.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

It's not safe to open SQL Server to the internet. A minimally responsible solution would place a VPN or a web server in between.

Having said that, you can remove a user from all groups (including public) and then grant only rights on specific stored procedures. Without db_datareader, a user cannot select * from SomeTable.

Andomar
  • 232,371
  • 49
  • 380
  • 404