Saturday, December 14, 2013

SQL Sever Login Issues

I have faced issues with SQL server logins in my career while connecting to SQL Server.

I have created a SQL Login with the following details

CREATE LOGIN [Publicuser] WITH PASSWORD=N'!@DFSRE$$%^', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Login created successfully.  When am connecting to SQL server using the Publicuser.I am getting error.

 Login Failed for user ‘Publicuser’ (Microsoft SQL Server, Error: 18456)



I have checked the error log using xp_readerrorlog

From eventlog I got the error message

Login failed for user 'publicuser'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]


Even though I have created the SQL server when installing SQL server I have given only windows authentication mode for login. So SQL server login users are forcedly disabled.

So I have enabled mixed mode. right click on server instance.





Select Security Tab.Select server authentication as SQL sever and Windows Authentication mode.



Click Ok.



I have to restarted the SQL sever Instance. then only the changes will reflect.
I have to restarted SQL Server Instance.








 Then i have Connected using "Publicuser" login.

If Login is disabled it will show this error.


you can enable the login account.






If Login user created and deny the user permissions.It will generates the below error.


Login failed for user 'Publicuser'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]



No comments: