SQL Server 2012 Developer Edition
In SQL Management Studio, if I connection with Windows Authentication and I am logged on as a domain user I get an error "logon failed for user 'domain\failname'" (SQL Server error 18456)
This is a new installation with default installation options. Reporting Services installed in Native mode but no further configuration done at this point.
The 'domain\failname' is in the SQL Server users list and is a member of the sysadmin role.
I can connect as a SQL Server user, (member only of the public role) or as another domain user ('domain\okname').
The domain user that fails, ('domain\failname') seems to have the same SQL Server settings as the successful domain user ('domain\okname'). The "domain\failname" is also a local administrator and also in the domain admin group.
SQL Server is configured to allow remote access with TCP/IP enabled for all services and the firewall has been modified to allow SQL Server through. Although my issue is not with remote access I did turn off the firewall to check.
Any suggestions as to what I have overlooked would be greatly appreciated.
Please create a SQL Server login for that Windows account using the following article:
If the SQL Server login exists, then make a right click on it, select Properties of the login, and verify it has access to the default database defined for that login. Maybe the default database for that login no longer exists.
Hope this helps
- Proposed as answer by Maggie LuoMicrosoft community contributor, Moderator Thursday, February 14, 2013 3:37 PM
- Edited by Alberto MorilloMVP, Moderator Thursday, February 14, 2013 3:48 PM
- Unproposed as answer by Siggy01 Thursday, February 14, 2013 9:26 PM
Thanks for your response Alberto.
Yes, the users are all added as SQL Server logins and since this is a new installation there are no other databases except for the Microsoft databases. All users reference the "master" database as the default database.
The difference between the "domain\failname" user and the "domain\okname" user is that the "domain\failname" user is an administrator and the SQL Server 2012 (and SSRS) may have been installed by "domain\failname".
I mention this because the "domain\failname" is listed as a user in the "master" database users list.
The successful users are not listed in the "master" database users list.
If I try to remove the "domain\failname" from the "master" database list (or if I try to clear the mapping check box from the user's SQL Server login properties) then I get the error:
"The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)"
The "domain\failname" is flagged only as a public user.
When I view the "owned schemas" I see that the RSExecRole has a black dot in the check box.
It seems to me that the SSRS is using "domain\failname" and this is preventing the "domain\failname" from connecting to the database engine in SQL Server Management Studio. The user is able to connect to the SSRS server.
Neither the SSRS's
- Service Account,
- Database Login, nor the
- Execution Account use this account.
Is the SSRS installation the underlying cause and if so will an uninstall and reinstall fix the issue or is there another solution?
Can you give a try for this:
If domain\okname is already exist in SQL logins, right click on it, and properties, then server roles, and click sysadmin, and give a try.
Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.
Not sure that I understand your suggestion, SohailMohammed:
domain\failname, which is already in the sysadmin role, fails to connect.
domain\okname, which is not in the sysadmin role, is able to connect.
How will adding the domain\okname to the sysadmin role help the domain\failname to connect?
Whether domain\okname is in sysadmin or not in sysadmin role has no impact on domain\okname connecting to the server and it also has no effect of domain\failname's inability to connect.