Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a created a local user called 'MynetDB' with SQL Server Authentication (password) in SQL server 2008. Server roles is public and user mapping to 2 databases as db_owner and public. Status has Permission to connect database engine: = Grant and Login: = Enabled.

When I try to connect to server database engine with SQL Server authentication it is giving error Login failed for user 'MynetDB'. (Microsoft SQL Server, Error: 18456)

I also tried from C# code giving same error. Please advise me how access the databases?

Edit: I am getting error on Management studio also

C#

<remove name="MyNetDB" />
    <add name="MyNetDB" connectionString="Data Source={ServerName};Initial Catalog=MynetDB;User Id=MynetDB;Password=taD6eg4c;" providerName="System.Data.SqlClient" />
share|improve this question
2  
Apart from the user, have you created a login? These are separate things. –  Oded Dec 27 '11 at 19:00
1  
check out this link this may help you to resolve how the user logs in to the Server via Domain or user account as a hint ... sql-server-business-intelligence.com/sql-server/error-code/… –  DJ KRAZE Dec 27 '11 at 19:01
    
Is the SQL instance set up to use SQL Server logins, or is it in Windows-only mode? –  Jon Seigel Jul 30 '12 at 18:11
add comment

migrated from stackoverflow.com Dec 27 '11 at 20:17

This question came from our site for professional and enthusiast programmers.

3 Answers

To figure out exactly what is happening, you should check the State that is reported in the message, either in the event log or SQL Server log. For example:

Error: 18456, Severity: 14, State: 8.

The state will usually help narrow down the exact cause. Here is a list of the states and their meanings:

Err  Message
 2   Invalid userid 
 5   Invalid userid
 6   Attempt to use a Windows login name with SQL Authentication
 7   Login disabled or password mismatch
 8   Password mismatch
 9   Invalid password
11   Valid login but server access failure
12   Valid login but server access failure
13   SQL Server service paused
18   Change password required
share|improve this answer
add comment

You need to go into SQL Server Mgmt Studio GUI, create the appropriate Windows login, and assign privileges to that Windows user or group, too.

share|improve this answer
2  
Not if the OP is using SQL Server authentication (which the OP states in the question). –  Thomas Stringer Dec 27 '11 at 19:01
    
@Shark - I got the impression that, by "local user", the OP meant "local Windows user". If he's using SQL Server authentication, then his MSSQL is running in mixed mode, and he can mix Windows and SQL server authentication. He just needs to understand the difference. And the difference between securing an "object" (database access) vs. "connecting" (which requires a valid Windows or MSSQL logon, and the privilege to access the default database). He's got several good suggestions so far... –  paulsm4 Dec 27 '11 at 19:53
add comment

Is the database name mynetdb?

Do you have access to the SQL Error log? If so, please post the message, or, if you can access the Windows Event Log on the server, you can post the error for the failed login from there.

This problem could also be because the login defaults to the master database, which the login may not have access to. When you look at the login properties, what is the default database? Is it master or is it the database you need access to?

It could be one of many possibilities. The complete error message would be more helpful

share|improve this answer
    
It wouldn't matter what the login's default database is because that is only used if no Initial Catalog is specified. The code posted shows he is setting the Initial Catalog to MyNetDB. Best bet is to start with the Error State –  sqlbattsman Aug 22 '12 at 1:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.