Error: 1474, Severity: 16, State: 1. Select all Open in new window. On server B, I am getting the following errors: The mirroring connection to 'TCP://serverA:5022' has timed out for database 'DB' after 10 seconds without a response. Check the service and network connections. Error: 1479, Severity: 16, State: 1. Select all Open in new window.
Login errors with Microsoft SQL Server (MSSQL) are a fairly common issue and can be easily solved with some basic troubleshooting steps. Before we dig in, let’s take a look at the details of the error to try and determine the cause.
- That error is telling you that the account that is running the SQL Server service doesn't have permission to the mdf file. Fix the permissions and it should attach just fine. Pro tip: pictures of text aren't as useful as the actual text.
- Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator. 18 Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is.
Solutions to Microsoft SQL Server Error 18456
Sometimes, the error presents as “login failed for user ‘<username>’,” this information will help us as we identify the user we need to troubleshoot. From the message, we’ll know the error number as a reference to search for next steps. In this case, it is Microsoft SQL Server, Error: 18456.
Other times, we may only see “Microsoft SQL Server Error 18456” along with the severity and state number. On its own, a state number might not mean much, yet it can offer more details as to what is wrong and where to look next.
These states of the error, 18456, are the most common. The descriptions and potential solutions offer a quick explanation and potential troubleshooting guide.
Step 1: Log In with Remote Desktop
The troubleshooting and solutions require you to login to the server or at least be able to make a Windows Authentication connection to MSSQL using Microsoft SQL Server Management Studio. The most common and easiest method is to connect directly to the server with a Remote Desktop Connection. If you need more information about Remote Desktop Connection, these Knowledge Base articles will help you get connected:
Step 2: Run Microsoft SQL Server Management
Once you are logged into the server, you’ll want to run Microsoft SQL Server Management Studio (SSMS). SSMS is the tool best suited to configure, manage, and administer MSSQL.
When you start SSMS, you will be asked to log in to the server. By default, most MSSQL servers have Windows Authentication enabled, meaning you must log in with the Windows Administrator or the account specified as the SQL Administrator when MSSQL was installed and configured.
In addition to Windows Authentication, MSSQL supports SQL Server Authentication. Depending on the version of MSSQL and how it was installed and configured, you may or may not have SQL Server Authentication enabled by default.
Step 3: Checking the Server Authentication Mode
Once we login to SSMS using Windows Authentication, we need to check the security settings to confirm whether MSSQL is set up to allow both Windows and SQL Authentication.
In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Properties.
Next, click the Security page.
If you find Windows Authentication is the only mode configured, this is the likely cause of Error 18456, Login failed for user ‘<username>’.
Setting the Server authentication mode to allow SQL Server and Windows Authentication, you will be able to login to MS-SQL with a SQL user and password or a Windows user and password. After making this change, you will need to restart the SQL Server service.
Step 4: Restart the SQL Service
In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Restart to apply the new authentication mode settings.
In the above example, Windows Authentication mode was the only mode configured, and the Error 18456 occurred because the user ‘sa’ is a SQL user and SQL Server Authentication was not permitted.
Step 5: Checking SQL User Permissions
As we check the SQL user permissions, we need to answer the following questions:
- Is the user allowed to log in?
- Does the user have a valid password set up?
- Does the user have the needed permissions for access to the desired database?
In SSMS Object Explorer, expand Security, Logins. Locate the user that was failing to log in. A x on the user indicates this user has login disabled.
To allow the user to login, right-click the user and choose Properties, then click the Status page. Enabling login for the user and click OK.
After refreshing the list user logins, we can confirm the user no longer has a red x present. This should allow the user to log in. In this example, the SQL user ‘sa’ failed to log in because there was no permission to log in.
Continuing with user troubleshooting, right-click the user and choose Properties, then click the General page. Here you can enter a new password and then enter the confirmation password. Click OK to save the new password. We set a new password for the user so that we are certain of the password when we attempt to log in.
Step 6: Mapping the User to the Database
Our last step in troubleshooting a user is to check user mapping to verify the user has access to the desired database and to set or verify their role for the database. Right-click the user and choose Properties, then click the User Mapping page. Select the Database from the list of databases. From the database role memberships, select the desired/required memberships. Click OK.
In this example, we mapped the user ‘ProdX709’ to the database Production X709.2019 and granted them database role db_owner. In many cases, you only need a user to have db_datareader and db_datawriter roles to be able to read and write to the database.
Conclusion
In this troubleshooting article, we learned how to identify specifics of Error 18456 to help us track down the root cause of the issue. Still looking for support? Our MSSQL database solutions come with assistance from our technical support team. Find out how our high-availability database can work for you!
Get Started Today!
We pride ourselves on being The Most Helpful Humans In Hosting™!
Our Support Teams are filled with experienced Linux technicians and talented system administrators who have intimate knowledge of multiple web hosting technologies, especially those discussed in this article.
Sql Server Error 1474 State 1 5
Should you have any questions regarding this information, we are always available to answer any inquiries with issues related to this article, 24 hours a day, 7 days a week 365 days a year.
If you are a Fully Managed VPS server, Cloud Dedicated, VMWare Private Cloud, Private Parent server, Managed Cloud Servers, or a Dedicated server owner and you are uncomfortable with performing any of the steps outlined, we can be reached via phone @800.580.4985, a chat or support ticket to assisting you with this process.
Related Articles:
Have you ever seen a situation where SQL Server mirroring is impacted because of external factors? One of my customers reported several of the following errors in the SQL Server ERRORLOG SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location. Let us learn about how to fix database mirroring error.
2016-11-10 07:05:27.90 Server Error: 1474, Severity: 16, State: 1.
2016-11-10 07:05:27.90 Server Database mirroring connection error 4 ’64(The specified network name is no longer available.)’ for ‘TCP://SQL1.sqlauthority.com:5022’.
2016-11-10 07:05:29.37 spid27s Error: 1474, Severity: 16, State: 1.
2016-11-10 07:05:29.37 spid27s Database mirroring connection error 4 ‘An error occurred while receiving data: ‘121(The semaphore timeout period has expired.)’.’ for ‘TCP://SQL2.sqlauthority.com:5022’.
As we can see above 1474 is a generic error. That can have many different OS errors. In above errors, it has come due to
- OS Error 64 = The specified network name is no longer available.
- OS Error 121 =The semaphore timeout period has expired.
Sometimes we can see error number, but not the message. Here is what you can do: I use net helpmsg command and pass the error number.
As a DBA, we need to look at more data to see and check if we can figure out what’s going on with the machine. In this customer’s scenario.
I reviewed Upon reviewing the System event log many networking warnings are being reported:
11/10/2016 07:05:27 AM Warning 27 e1rexpress HP Ethernet 1Gb 2-port 361T Adapter #3 Network link is disconnected.
11/10/2016 07:03:34 AM Warning 461 CPQTeamMP Team ID: 0 Aggregation ID: 1 Team Member ID: 0 PROBLEM: 802.3ad link aggregation (LACP) has failed. ACTION: Ensure all ports are connected to LACP-aware devices.
11/10/2016 07:03:10 AM Warning 27 e1rexpress HP Ethernet 1Gb 2-port 361T Adapter #3 Network link is disconnected.
11/10/2016 07:03:11 AM Warning 27 e1rexpress HP Ethernet 1Gb 2-port 361T Adapter #2 Network link is disconnected.
Above are at the same time when SQL is reporting errors. Error in the system event logs is indicating that there is something going on with the network cards which needs to be addressed. I informed my client that I am not familiar with this error but I am sure your hardware folks would be able to tell us what this is and how to fix it, if it is a problem.
When we contacted the hardware team for this customer they informed that NIC card drivers are very old and not compatible. As soon as NIC card drivers were upgraded, issues were resolved. Let me know if you have faced any similar error about – database mirroring error.
Sql Server Error 1474 State 1 Hour
Reference: Pinal Dave (https://blog.sqlauthority.com)