How to Fix MS SQL Server Error: 18456?

SQL Server is the most commonly used Relational database management by many organizations to maintain their database. The primary function of the SQL server is to store and retrieve data from the database as per the application request. SQL server provides data management on the top of the core database management system. Server authenticates the valid user by login credentials. The authentication phase may sometimes prompt error messages due to several reasons. SQL Server Error 18456 is one such error where the Server fails to authenticate the user.

Error details
Error ID: 18456
Symbolic Name: LOGON_FAILED
Message: "Msg 18456, Level 14, State 1, Server, Line 1” or “Login failed for user’’
This error occurs mostly when the login to the server fails. The error message includes account distributor_admin and issues with account replication. The repl_distributor created by the replication allows distributor and publisher to communicate. The distributor_admin must possess a valid password to login to remote server.

Common Reasons Behind the SQL Server ERROR 18456

There can be several reasons behind the error. The common causes behind the login failure can be due to any of the below mentioned cases.

• SQL Authentication is not enabled in the server.
• Invalid user ID or Password.
• Misspelled user ID or Password.
• Expired password due to any fatal errors.

The error state displayed along with the error message indicates the cause of the error. So that user can easily carry out appropriate actions to fix the error. Some common error states and description are given below.

• State 2 or 5 indicate Invalid user ID
• State 7 shows Login disabled and password mismatch error
• State 8 is for Password mismatch
• Invalid password is denoted by state 9.
• 11 and 12 states indicate Valid login but the server access fails due to other reasons
• State 18 indicates that password Change is required

How to Overcome the Above Situations?

The first and foremost solution is to check whether the SQL Authentication mode in enabled on the Server. Before that, make sure that the password and the username are not misspelled or incorrect. So that error due to manual mistakes can be eliminated.

How to Enable the SQL Authentication Mode?

Use SQL Server Management Studio to configure or change the properties of the SQL server. The following steps help you to enable SQL authentication on SQL Server 2008.

1. Connect to SQL server using Windows Authentication mode.
2. Right click on SQL Server and go to its properties as shown in the figure.
   
3. Select the security settings from the server properties window

4. Enable the option SQL server and Windows Authentication Mode and click OK.

5. Restart the server after setting the authentication mode to SQL Server Authentication mode. The figure shows the restarting of SQL Server 2008.

This helps you to fix the user Login failure with error ID: 18456 if SQL server authentication mode was not enabled in the server.

Observation

SQL Server error 18456 occurs if the server fails to authenticate the user and thereby user login fails. This can happen if the SQL server authentication mode is not enabled in the server. This can happen due to manual errors such as misspelled password or username. The above mentioned procedure helps you to enable SQL Authentication mode on SQL server 2008 thereby helps you in fixing error: 18456, the user login failure. But If you forget the password of your crucial SQL database file, then to reset SQL Admin password you need an advanced SQL password recovery utility which allows you reset lost MS SQL database password and regain access on your database.


Appreciate your suggestions. Please comment your feedback and reach me out via email

Prerequisites for Configuring Log Shipping

1)      Both SQL & OS Editions and Versions should be at same level on all participating servers

2)      Express Edition doesn’t supports SQL server Logshipping

3)      The user who configures the Logshipping must be member of sysadmin server role

4)      SQL Server and SQL Server agent services must be configured under windows authentication account with sysadmin permissions on all participating server

5)      Recommended to have unique logon account for both SQL Server and SQL Server agent services

6)      The Logon account must have at least local administrator privileges to carry out the administrative tasks

7)      SQL Server port (1433) must be opened at network level for communication between the both servers

8)      Sharing ports must be enabled on both production & DR servers for log shipping

9)      Create shared network share on both Primary and secondary servers

10)   The backup and restore directories in your log shipping configuration must follow these requirements.

For the backup job, read/write permissions to the backup directory are required on the following:

·         The SQL Server service account on the primary server instance. 

·         The SQL Server Agent account on the primary server instance. 

·         For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the service account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.

For the restore job, read/write permission to the copy directory are required by the following:

·         The SQL Server service account on the secondary server instance. 

·         The SQL Server Agent account on the secondary server instance. 

11)   SQL service log on accounts must be same on both Environments.

12)   Database being log shipped must be in Full recovery model or Bulk logged recovery model. The simple recovery model is not supported

13)   Should not run any maintenance plan or backup job against log shipped databases since that would break Logshipping chain

14)   Logshipping configuration can be done with in trusted domain computers or work group computers

15)   Recommended to have the same Drive Space capacity in fact more space of secondary server considering the data growth in future

16)   Ensure you do not hardcode your server name in your application connection connecting the databases. Create in a such way that you can switch easily to the database servers and take advantage of log shipping

Appreciate your suggestions. Please comment your feedback and reach me out via email

Performance Dashboard Error: Could not find stored procedure 'msdb.MS_PerfDashboard.usp_CheckDependencies'.

In this post, I’m going to talk an issue that when you try to launch the main Dashboard Report in SQL Server 2008 R2. You may receive the below error message that includes information about the cause of the error, which can help you to resolve the issue.

Performance Dashboard Error: Could not find stored procedure 'msdb.MS_PerfDashboard.usp_CheckDependencies'.

Cause

Microsoft does not provide the Performance Dashboard for SQL Server 2008R2 for download, but there is a work-around by installing the Performance Dashboard for SQL Server 2005 and modifying the provided Performance Dashboard T-SQL setup script from Microsoft.

For 2005/2008/2008R2 download here 
For 2012 download here

Resolution

First install the setup file. By default it is installed in: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard. Once you install this, You will find two files at the installed location. Traverse to C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard 

After installing the Dashboard report setup, now setup the dashboard report using SSMS and have to run setup.sql script.

Connect to SQL Server using SSMS and open the file, setup.sql, located in C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard.

Find the code in setup.sql. Go to the statement where you see 'select @ts_now = cpu_ticks' as shown below

Change the statement to

select @ts_now = ms_ticks from sys.dm_os_sys_info

In below screenshot, I have modified select @ts_now = ms_ticks from sys.dm_os_sys_info

Now, execute the “setup.sql” script. 

Now you can right-Click the SQL Server, navigate to the server level reports section, select Custom Reports.In below screenshot, I select "Custom Reports". 

Read My Complete Article "Here" 

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Email: gana20m@gmail.com

Linkedin

For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

Always on database in suspect mode and suspended data movement.

Always on database in suspect mode and suspended data movement.

What is suspect mode of database?

SQL database is marked as suspect when primary group file is corrupt and when SQL Server tries to start, database cannot be recovered during that time. 

There can be a lot of reasons for a database to go in suspect mode, some of them are:

• Hardware failure

• Improper shutdown of the database server

• Corruption of the database files

• Unavailable device files

• Unavailable database files

• Database resource used by operating system

• SQL Server incorrectly asserts free data page space when a row is inserted

Issue:

My drive where the 'tempdb' is located is completely filled up and there is no space left on drive causing the 'tempdb' in suspect mode.

This also suspended the data movement between databases in Always on group from primary to secondary.

Step 1:

Identified tempdb filled up the drive space and database is in suspect state

    Step 2: 

    Restarted SQL services to clear space on tempdb db and Now the database is online but the Always-on dashboard report shows critical warning and database is not synchronizing. 

    Secondary replica database data movement is suspended (fail-over dash board report shows data movement got suspended)

    Step 3:

    Expand Always-on High Availability pane and select availability databases.

    Right click on the database and select resume data movement.

     Step 4:

     Issue resolved. Now the database is completely synchronized.

    Appreciate your suggestions. Please comment your feedback and reach me out via email

Regards,

Ganapathi varma

Senior SQL Engineer, MCP

For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

Linkedin

Troubleshooting With Damaged Database File in SQL Server

The SQL Server performs its input/output operations by using Windows API’s like ReadFile, ReadFileScatter, WriteFile and WriteFileGather. After these input/output operations, SQL server views those errors that are related to these APIs. If any of these APIs call fail with Operating System error, then SQL Server displays the Error 823.

There is a logical consistency problem encountered with input/output operations while transferring the data. Error 824 displays this logical consistency.

What is Error 824?

In SQL server, Error 824 indicates that there is a major problem in the storage system or in the hardware part. The Error is encountered when the database file is found damaged. This Error also indicates that the SQL server finds something wrong on the page while Windows didn’t find any error in the page.

Causes of Error 824

The error 824 shows the following information:

• The suspected file of the database on which the input/output operation is executed.
• To which database this file belongs.
• Which page number was involved while performing input/output operation?
• Was the performed operation a write or read operation?

As this Error indicates to issues with the storage system or the hardware part of the system, so for the solution of this problem, it is very important to look for issues in the hardware.

Inspection of Hardware Failure

First, check whether it is a hardware problem or not, if it is a hardware failure problem, then check the hardware and fix the problem. Otherwise, if it is a data corruption issue, then try to exchange various hardware components to segregate the problem.

Finally, it can be useful to change to a new hardware system. This change may include, re-formatting of the disk drives and installation of the operating system again.

Note:

If the problem is not related to the hardware and you have a clean backup, then restore your database from backup.

Note:

Use PAGE_VERIFY CHECKSUM option while changing the databases.

Use Following Steps To Solve The Problem Manually

• Check the suspected pages in msdb.
• Calibrate the database consistency.
• Quickly turn On the PAGE_VERIFY CHECKSUM option if it is off.
• Check the Windows Event logs for the error that is reported by the Operating System.
• Utilize the SQLIOSim.
• Work with your device manufacturer.
• Evaluate the filter drivers if available.

In case the following manual method won’t work, then you should go for a third party SQL recovery tool.

Microsoft will release public preview of SQL Server 2016 this summer

Big News from Microsoft.

For complete information refer the below links.

http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/


http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx

How to add new disks to existing SQL Server Failover cluster.

Hello everybody, In this post I’m giving the procedure to add the new clustered disks to existing SQL Server failover cluster.

My Environment: Windows 2008 Enterprise Edition, MS SQL Server 2008 R2 Enterprise Edition

Disk Letters : 'O', 'P', 'R'

Downtime: Zero - '0' ( No service disruption or Zero Downtime)

Lets begin....

My storage team has already given the above 3 LUN's through iSCSI initiator or RDM.

Request from client was to add these disks to the existing windows cluster in order to use them as clustered disks for SQL Server cluster.

Step 1:

To open disk management. Go to RUN--> diskmgmt.msc

You should able to see the disks in offline state on all the nodes available in cluster.

Step 2:

Bring the disks to online in any one of the nodes in the cluster (you can choose only one node). On other nodes it should be in offline mode as we are going to use these disks for failover cluster.

Step 4:

Now the disks are online and unallocated. We have to create a new volume assigning a new drive letter. 

Format the drive with NTFS file system and click finish.

This is how it looks in disk management console.

Step 5: 

Now we are ready to add this disks to windows fail over cluster.

a) click on disk option available on storage pane in fail over cluster manager console
b) It will prompt for all the available disks can be added to cluster
c) select the disks and click OK

Now the disks are added to available storage pool in cluster manager console.

You can also see this disks available from my computer.

Hold on... It's not done yet. you are done with only adding disks in windows cluster. Now we have to add these disk and will be going to use for SQL cluster.

Step 6:

Now Right click on SQL Server and select add storage. It will prompt all the new available disks from storage. Select the disks and click ok.

Step 7:

Now Right click on SQL Server and select properties of SQL Server. Now we have to add the dependencies for new disks as shown below.

If the disks are not part of dependency list, click insert and add disks and click OK.

You can find the dependency of SQL Server by verifying dependency report.

You can also verify the disks by creating the sample databases on newly added disk as shown below :)

Regards,

Ganapathi varma

Senior SQL Engineer, MCP

Reference: (http://mssqllover.blogspot.in)

Linkedin