A Software Engineer and data professional's blog on SQL, data, databases, data architectures, data management, programming, Software Engineering, Project Management, ERP implementation and other IT related topics.
30 October 2018
SQL Server Troubleshooting: Login Failed for User
Login failed for user '<domain>\<computer>$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 5.
From the text it seemed like a permission problem, thing confirmed by the documentation (see [1]), the Error Number and State correspond to a „User Id is not valid“ situation. In a first step I attempted to give permissions to the local account (dollar sign included). The account wasn’t found in the Active Directory (AD), though by typing the account directly in the “Login name” I managed to give temporarily sysadmin permission to the account. The error continued to appear in the error log. I looked then at the accounts under which the SQL Services run - nothing suspect in there.
Except the error message, which was appearing with an alarming frequency (a few seconds apart), everything seemed to be working on the server. The volume of records (a few hundred thousands over a few days) bloating the error log, as well the fact that I didn’t knew what’s going on made me take the time and further investigate the issue.
Looking today at the Windows Logs for Applications I observed that the error is caused by an account used for the Microsoft SQL Server IaaS Agent and IaaS Query Service. Once I gave permissions to the account the error disappeared.
The search for a best practice on what permissions to give to the IaaS Agent and IaaS Query Service lead me to [2]. To quote, the “Agent Service needs Local System rights to be able to install and configure SQL Server, attach disks and enable storage pool and manage automated security patching of Windows and SQL server”, while the “IaaS Query Service is started with an NT Service account which is a Sys Admin on the SQL Server”. In fact, this was the only resource I found that made a reference to the IaaS Query Service.
This was just one of the many scenarios in which the above error appears. For more information see for example [3], [4] or [5].
References:
[1] Microsoft (2017) MSSQLSERVER_18456 [Online] Available from: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-2017
[2] SQL Database Engine Blog (2018) SQL Server IaaS Extension Query Service for SQL Server on Azure VM, by Mine Tokus Altug [Online] Available from: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/10/25/sql-server-iaas-extension-query-service-for-sql-server-on-azure-vm/
[3] Microsoft Support (2018) "Login failed for user" error message when you log on to SQL Server [Online] Available from: https://support.microsoft.com/en-sg/help/555332/login-failed-for-user-error-message-when-you-log-on-to-sql-server
[4] Microsoft Technet (2018) How to Troubleshoot Connecting to the SQL Server Database [Online] Available from: Engine https://social.technet.microsoft.com/wiki/contents/articles/2102.how-to-troubleshoot-connecting-to-the-sql-server-database-engine.aspx
[5] Microsoft Blogs (2011)Troubleshoot Connectivity/Login failures (18456 State x) with SQL Server, by Sakthivel Chidambaram [Online] Available from: https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/06/troubleshoot-connectivitylogin-failures-18456-state-x-with-sql-server/
18 June 2017
SQL Server Administration: Database Recovery on SQL Server 2017
Plan A - Create the database with ATTACH_REBUILD_LOG option:
As it seems the option is available in SQL Server 2017, so I attempted to create the database via the following script:
CREATE DATABASEON (FILENAME='I:\Data\ .mdf' ) FOR ATTACH_REBUILD_LOG
And as expected I run into the first error:
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "I:\Data\
Msg 1802, Level 16, State 7, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
It looked like a permissions problem, though I wasn’t entirely sure which account is causing the problem. In the past I had problems with the Administrator account, so it was the first thing to try. Once I removed the permissions for Administrator account to the folder containing the database and gave it full control permissions again, I tried to create the database anew using the above script, running into the next error:
File activation failure. The physical file name "D:\Logs\
Msg 1813, Level 16, State 2, Line 1 Could not open new database '
This approach seemed to lead nowhere, so it was time for Plan B.
Plan B - Recover the database into an empty database with the same name:
Step 1: Create a new database with the same name, stop the SQL Server, then copy the old file over the new file, and delete the new log file manually. Then restarted the server. After the restart the database will appear in Management Studio with the SUSPECT state.
Step 2: Set the database in EMERGENCY mode:
ALTER DATABASESET EMERGENCY, SINGLE_USER
Step 3: Rebuild the log file:
ALTER DATABASE <database_name>
REBUILD LOG ON (Name=’_Log ',
FileName='D:\Logs\.ldf ')
The rebuild worked without problems.
Step 4: Set the database in MULTI_USER mode:
ALTER DATABASESET MULTI_USER
Step 5: Perform a consistency check:
DBCC CHECKDB () WITH ALL_ERRORMSGS, NO_INFOMSG
After 15 minutes of work the database was back online.
Warnings:
Always attempt to recover the data for production databases from the backup files! Use the above steps only if there is no other alternative!
The consistency check might return errors. In this case one might need to run CHECKDB with REPAIR_ALLOW_DATA_LOSS several times [2], until the database was repaired.
After recovery there can be problems with the user access. It might be needed to delete the users from the recovered database and reassign their permissions!
Resources:
[1] In Recovery (2008) Creating, detaching, re-attaching, and fixing a SUSPECT database, by Paul S Randal [Online] Available from: https://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/
[2] In Recovery (2009) Misconceptions around database repair, by Paul S Randal [Online] Available from: https://www.sqlskills.com/blogs/paul/misconceptions-around-database-repair/
[3] Microsoft Blogs (2013) Recovering from Log File Corruption, by Glen Small [Online] Available from: https://blogs.msdn.microsoft.com/glsmall/2013/11/14/recovering-from-log-file-corruption/
25 May 2009
DBMS: Object Permissions (Definitions)
"These are permissions that enable a user to work with data in an object. For example, SELECT is the object permission that enables a user to read data from a table object." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)
"Permission based on a table or view; controls the ability to execute the SELECT, INSERT, UPDATE, and DELETE statements against the table or view." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)
"An attribute that controls the ability to perform operations on an object. For example, table or view permissions control which users can execute SELECT, INSERT, UPDATE, and DELETE statements against the table or view." (Anthony Sequeira & Brian Alderman, "The SQL Server 2000 Book", 2003)
"A permission on a database object that controls how the object can be accessed." (Marilyn Miller-White et al, "MCITP Administrator: Microsoft® SQL Server™ 2005 Optimization and Maintenance 70-444", 2007)
"Permissions that regulate the use of certain commands (data modification commands, plus select, truncate table and execute) to specific tables, views or columns." (Karen Paulsell et al, "Sybase SQL Server: Performance and Tuning Guide", 1996)
"Object permissions regulate a user’s ability to work with the data contained in the database." (Joseph L Jorden & Dandy Weyn, "MCTS Microsoft SQL Server 2005: Implementation and Maintenance Study Guide - Exam 70-431", 2006)
DBMS: Permissions (Defnitions)
"A permission is a right to do something in a database. Examples include performing a database function (such as creating table) or working with an object (INSERT)." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)
[statement permissions:] "These are database permissions and enable users to create objects, drop objects, or modify objects in a database. Statement permissions do not work with data, but rather work with the containers that hold the data." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)
"Authorization that enforces database security. SQL Server permissions specify the Transact-SQL statements, views, and stored procedures each user is authorized to use. The ability to assign permissions is determined by each user's status. There are two types of permissions: object permissions and statement permissions." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)
[statement permission:] "Permission that controls the execution of Transact-SQL statements that create database objects or perform certain administrative tasks. Can be granted, revoked, or denied." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)
"These are permissions that regulate a user’s ability to create structures that hold data, such as tables and views." (Joseph L Jorden & Dandy Weyn, "MCTS Microsoft SQL Server 2005: Implementation and Maintenance Study Guide - Exam 70-431", 2006)
"A privilege that you grant to a principle. When authorized, the principle may then interact with one or more securables." (Robert D. Schneider and Darril Gibson, "Microsoft SQL Server 2008 All-In-One Desk Reference For Dummies", 2008)
"Operations that can be applied to or done with an object. Example file permissions are read, write, and delete." (Mark Rhodes-Ousley, "Information Security: The Complete Reference", 2nd Ed., 2013)
"Permissions placed on objects within a database. Database permissions specify which actions a database user can perform on tables, views, stored procedures, and other objects." (Mark Rhodes-Ousley, "Information Security: The Complete Reference" 2nd Ed., 2013)
"The definitions of what object access actions are permitted for a specific user or group." (Weiss, "Auditing IT Infrastructures for Compliance" 2nd Ed, 2015)
"The type of authorized interactions that a subject can have with an object. Examples include read, write, execute, add, modify, and delete." (Shon Harris & Fernando Maymi, "CISSP All-in-One Exam Guide" 8th Ed, 2018)
About Me
- Adrian
- IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.