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

I am running SQL Server 2005 x64 SP2 and have problem connecting to database with the SQL logins I created. I test the database login using Microsoft ODBC Administrator to connect the the database locally, only 'sa' can login. All other sql logins I created fails and returns this message:

Microsoft SQL Server Login

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'doteasylogin'.

OK

In the SQL Logs, it shows: Error: 18456, Severity: 14, State: 12.

The SQL Services is running as 'Local System'.

Can anyone help please?

Did you create the user at the server level and give him access to the server (and to the database) ? The state 12 indicates that there is an access problem which is normally dedicated to the fact that the user is either not allowed to login or does not have the appropiate permissions for the database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I created the user 'testlogin' in SQL Server Login and grant the 'testlogin' user db_datareader & db_datawriter permission in Database Role Membership.

I'm using Windows Server 2003 x64 standard edition.

|||

Try executing the following two queries and check that each of them returns a row:

select name, principal_id from sys.server_principals where name = 'testlogin'

select permission_name, state_desc from sys.server_permissions where grantee_principal_id = suser_id('testlogin')

The first query should return the 'testlogin' login name and its associated id.

The second query should display a row with the CONNECT_SQL, GRANT value.

Thanks

Laurentiu

|||

Hello.

I have the same problem.

I am a beginner in SQL Server.I tried to install SQLServerExpress2005sp2

I've migrated a database from Access.

I tried to make a new user: for example 'testlogin' .

I've do all you sugest (server permissions, database permissions, ..)

I've run the queries from above and everything seems ok, but I cannot connect to database using the 'testlogin' account.

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

Login failed for user 'testlogin'. [CLIENT: xx.xx.xx.xxx]

IMPORTANT: if I grant sysadmin role to 'testlogin' I can connect.

thanks a lot in advance.

|||I started receiving this error after attempting to set up database mirroring. After running through the configuration including setting up endpoints (unsuccessfully) the mirror server has stopped accepting logins from SQL accounts (except SA). The principal server still accepts SQL logins without failure. I have stopped mirroring and deleted the database on both servers. Thank you for any help you can give. This was not an issue before I attempted database mirroring.

Okay after looking at it further it turned out that in my instance there was a new object for mirroring created. This object was accessible by going to the properties of the user account, choosing securables on the left side of the window. Then click on the add button and choose "all objects of the types" click OK; check Endpoints and click OK. In my case I saw the "Mirroring" option under Securables. That seems to be the culprit, after selecting Grant for the Connect permission I was able to connect fine. As a temporary solution I have chosen to follow the steps mentioned before using the "Public" server role, that way all accounts will have connect access. This is just temporary until I learn how to Drop the pesky Mirroring object from the server altogether.
|||

I am having the same issue, create a login using TSQL and granting access to a user database and it will not connect. I have tried the queries mentioned and from sys.server_permissions is saying CONNECT SQL with the GRANT option.

As soon as I added the user to the sysadmin server role, no problems connecting.

Thank you.