July 10, 2008 at 4:45 am
Hi,
i checked by log files and found n number of failed login attempts into ms sql server express 2005 as user: sa from multiple ip addresses, what do you guys recommend is there a way in sql server in which it can put a temporary ban on the ip address that fails for multiple times like 5 times and ban holds for some hours then releases the ip address or is there any other way you recommend to do with this problem.
Please recommend
Thanx
July 10, 2008 at 5:03 am
Not from within SQL.
Are you running with mixed authentication mode?
If you are, you can in SQL 2005 disable or rename the sa account. That gives you a little more security, as even if they guess the password, they can't get it.
Do you have any idea where the connections are coming from? Is your server on a machne that's web-facing? If so, make sure there's a good firewall in place and you can limit connections to SQL to specific IPs that you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2008 at 5:41 am
hi Itoso,
Ensure that you are behind the firwall if it is a web server/sql server.
July 10, 2008 at 6:00 am
hi,
thanx for prompt replies guys,
well the server is web facing is hosting many websites and we have to give sql server management access to people so i think the best way is to rename the sa account, so can you please tell me the way to rename the sa account.
thanx
July 10, 2008 at 6:47 am
Hi Itoso,
WARNING: The built-in SA user account is installed without a password. This enables you to connect to MSDE initially. However, if you enable SQL Server Authentication, you must create a password for this account immediately. To create a password for the built-in SA account, follow these steps to use OSQL, which is a command-line utility that is installed with MSDE:
1. On the computer that is hosting the instance of MSDE that you are connecting to, open the command prompt window.
2. Type the following command, and then press ENTER:
osql -U sa
This connects you to the local, default instance of MSDE by using the SA account.
3. Type the following commands on separate lines, and then press ENTER:
NOTE: Make sure that you replace 'mynewpassword' with the new password.
sp_password null, 'mynewpassword', 'sa'
go
Notice that you receive the following message, which indicates that your password was changed successfully:
"Password changed."
Reference:
http://support.microsoft.com/kb/319930
Also if you are behind a firewall & block the IP's, please do with the help of your Network Admin.
Regards,
Rajesh
July 10, 2008 at 7:01 am
could it be that someone has configured an application\web site to use the SA account? either way assign an obscure password to the account and then disable it. Use the IP addresses and attempt to find out who is trying to connect.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 10, 2008 at 8:51 am
You can use logon_triggers to limit your exposure and to log the IPs from where the attempts were made.
* Noel
July 10, 2008 at 8:52 am
Hi,
the password is already in place can you tell me the way i can rename the sa account to some other name so that people can never guess the password by using pograms guessing everything.
thanx
July 10, 2008 at 8:57 am
BAD-DBA-DAB (7/10/2008)
Reference:
That's for MSDE (SQL 2000). The Original Poster is using SQL Express (SQL 2005)
ltoso: You can rename the sa account the same way you rename any other account. Easiest way is from management studio express, expand out the logins folder, right click the SA account and select rename.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2008 at 11:01 pm
Hi,
I think its not easy to change sa account name, instead you can change the password of sa account, its compatively easier.
nkgupta
July 10, 2008 at 11:22 pm
Hi,
another question poped into my mind, if we rename the sa account will the sa account privileges be transfered for all databases to the new account the we have renamed.
please recommend
July 10, 2008 at 11:38 pm
ltoso (7/10/2008)
Hi,another question poped into my mind, if we rename the sa account will the sa account privileges be transfered for all databases to the new account the we have renamed.
please recommend
Yup. It's still the sa account, SID 0x01 with all the implications of that. It just has another name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2008 at 1:13 am
I agreed with Gila's previous posts, it's very straightforward to rename the sa account and just as easy to assign a strong sa password. Even better to disable the sa account if possible...
July 11, 2008 at 5:45 am
This site is sometimes useful to locate the IP Address source:
July 11, 2008 at 6:03 am
assuming he is seeing a public IP address, my guess is he isn't
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply