September 16, 2008 at 3:35 am
Hi,
Since morning I have observed the following errors in sql 2005 error logs
Error: 18456, Severity: 14, State: 16.
Login failed for user 'sa'. [CLIENT: XXX.XXX.XX.XXX]
(where XXX represents Ip address of client machine)
After that the client machines are not getting connected to sql server. Actually I have 2 applications connected to the same sql server. One is working perfectly fine while the other is not able to connect. Both the applications are connected through sa.
Can someone please help me to solve this issue.
Note : SQL Server version is SQL Server 2005 standard Edition with SP2
September 16, 2008 at 4:30 am
State 16 means that the default DB or the DB requested by the login is not available. Check what DB the app in question wants and make sure that it's accessible (present and online)
Why are your apps using the sa login? It's a security worst practice.
In the future, please post SQl 2005-related questions in the SQL 2005 forums. Thanks
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
September 16, 2008 at 5:32 am
HI,
The db in question is accessalable and online. Previously a different login was given but I read at several places that it might be access issue hence i logged in the application through sa. But still the same issue exist.
Please assist.
Thanks
September 16, 2008 at 5:39 am
Can you check the connection string that the app uses, see if it specifies an initial database. Also check what the default database is for sa.
A properly set up account for your app won't give problems and allows you to limit what the app is allowed to do.
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
September 17, 2008 at 3:15 am
Hi,
Thanks for your reply..
I have mapped the application server to sql server 2000 with just a change in database server ip address. There it's working perfect.
Is this the issue related to sql server 2005 ?
If yes, what is the solution ?
Thanks..
September 17, 2008 at 6:34 am
It's not a SQL 2005-specific problem. The error (specifically the state) is saying that the database requested by the login isn't available, either because it's not there, is offline or because the login doesn't have permission.
The login's sa which will always have permission, so that isn't the issue. Check what the default DB is for sa (should be master) and check what database is requested in the app's connection string.
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
September 17, 2008 at 8:59 am
Since the other application is accessing the same database, there is no way that the database is not available or offline.
The default db for 'sa' is master but this default exist in sql 2000 as well and there I have no issues. Even the other application is able to access the db with default db as master.. I guess it might be some other issue.
September 17, 2008 at 9:33 am
Tanveer (9/17/2008)
The default db for 'sa' is master but this default exist in sql 2000 as well and there I have no issues.
That's as it's supposed to be.
Do you have any way of seeing the connection string that the app that's not working uses?
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
September 17, 2008 at 11:38 pm
I don't have rights to see the connection string.. As mentioned earlier, the application uses the same connection string to connect to sql server 2000 and there I don't have any issues
September 18, 2008 at 1:34 am
Then there must be something different about the databases that are on SQL 2000 and the databases that are on SQL 2005. The thing is, the error is saying there's something wrong with the DB access or the DB.
Is there anyone you can ask about the connection string? All that I want to see from it is the requested database.
In the meantime, can you please run the following queries?
SQL 2000:
select name, status, status2 from master..sysdatabases
SQL 2005:
select name, user_access_desc, is_auto_close_on, is_in_standby, state_desc from sys.databases
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
April 30, 2009 at 8:16 am
I had this problem and I brought the database offline and then online and that resolved the issue.
August 5, 2009 at 6:49 am
Hello...Having the same issue with a vendor app, who has suggested entering fake IPs in web page config files. Will push back on that resolution, but I'm a little confused as to what the problem is. I've validated the SQL login and it's privs.
KU
November 24, 2010 at 2:40 am
I am getting the same problem and I am not able to login using sa
Error is coming:Login failed for user sa Microsoft Error:18456
Its Urgent...
Thanks
November 24, 2010 at 5:18 am
Here is the deal I have found. My native SQL User accounts have the account policy so they follow the same stipulations as the active directory domain accounts. When users lock that account or a password miss match or something like that occurs you cannot just unlock. We have spent some time and testing to nail down a solution. We found that we have to drop the login account (not the user account in the database) and then re-create the account. This means you either need the password to the account or you need to create a drop script and recreate script.
1. Unlock
2. Get drop create login scripts
3. run drop
4. run create
All mappings and permissions will be intact. This is what worked for us.
October 18, 2011 at 9:26 am
-Removed-
Kev -=Conan The Canadian=-
@ConanTheCdn
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply