March 17, 2015 at 7:15 am
Hi.
One of the security reason.
What is best method for disable SQL "sa" account after installation done? or rename that account?
I have done disable one of the database, but as below error reported in error log file, that might be database background process running "sa" account, Is it correct?
Message
Error: 18470, Severity: 14, State: 1.
Message
Login failed for user 'sa'. Reason: The account is disabled. [CLIENT: <local machine>]
Thanks
March 17, 2015 at 7:27 am
SQL Galaxy (3/17/2015)
Hi.One of the security reason.
What is best method for disable SQL "sa" account after installation done? or rename that account?
I have done disable one of the database, but as below error reported in error log file, that might be database background process running "sa" account, Is it correct?
Message
Error: 18470, Severity: 14, State: 1.
Message
Login failed for user 'sa'. Reason: The account is disabled. [CLIENT: <local machine>]
Thanks
The way to disable it is, quite simply:
alter login [sa] disable;
If you're getting a connection error, it simply means that someone's trying to connect using the login. While I don't know your particular circumstances, let me encourage you to keep the sa login disabled. It's the right thing to do from a security perspective. Developers, applications and you (presumably the DBA) should not be using it. It really should be disabled and left that way. If it's disabled, then you don't need to rename it.
March 17, 2015 at 7:27 am
No, that's something external trying to connect. The system tasks don't need to log in. They're internal.
I prefer disable, as I've seen a case before where a service pack installation failed because sa was renamed.
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
March 17, 2015 at 10:55 pm
Thank you inputs from Experts..
As per Error messages
Message
Error: 18470, Severity: 14, State: 1.
Message
Login failed for user 'sa'. Reason: The account is disabled. [CLIENT: <local machine>]
sa account has been used server itself not in external because CLIENT is local machine. client IP will be recording here if client connected.
How can an identified this account has been used at internal?
This database has been configured as below features.
1. SSRS configured that same server and database
2. Database High availability Mirroring configured, principal, Mirror and witness are in different servers.
Can be possible to identified sa account where it is using? if any script for getting sa login using internal as well as application side.
Thanks
March 18, 2015 at 2:53 am
There is some external application running on the local server connecting to SQL as sa.
It is not an internal SQL background process, they do not need to log in and will never have a login failure. Go through what's running on the local server and connecting to SQL and see which is using sa.
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
March 18, 2015 at 5:25 am
Like Gail said, there is something external that's trying to connect. Since the login failed, nothing else is going to be logged, so I don't know of any script that you're going to be able to run to determine where the connection is coming from.
If you can't find what it is that's trying to connect, you could try looking for something that's failing. If the application can't login, it isn't going to succeed in doing whatever it is that it's trying to do. If there aren't any notifications defined in the case of application failure, you might try the event logs to see if anything's being logged there.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply