How to Prevent SQL Database Corruption in An Efficient Manner

Overview

As many companies rely on SQL Server Database for the storage of their data, database administrators need to make sure that the database is made available whenever required without failure. In order to ensure that, he/she needs to make sure that the data integrity is maintained all the time and the database files is free from corruption issues. As any other file formats, SQL Server Database files can be corrupted, which may even lead to permanent loss of data. In the content, we will be focusing about the corruption issues and learn ways to prevent Corruption in SQL Server.

About SQL Database Corruption

The top most priority among all the concerns that DBAs needs to be worried are disaster prevention and recovery of SQL Server Database. The SQL Server Database is said to be corrupt state if a problem arises with the improper storage of the actual zeroes and ones required to store data at disk or I/O subsystem. Corruption can also be caused by errors or failures in the storage media or the irregular system shutdown. There can be several reasons behind the database corruption:

Causes of SQL Database Corruption

Following are the database corruption causes:

• Almost all the causes of corruption in SQL Server Database is related to issues at the IO subsystem level i.e., problems related to drives, controllers and even drivers.
• One thing to keep in mind is to use disk-check utility (CHKDSK) available in most of the systems to scan bad sectors, modifies entries or other storage issues that can lead to corruption.
• SQL Server depends on disk system to store/retrieve data, which is why most of the disk related issues leads to corruption.
• Another reason are usage of Anti-virus or Kernel drivers can lead to corruption that is why Experts recommend to exclude SQL Server database files at the time of anti-virus scans.
• Keeping SQL Server Database files in compressed format to conserve disk storage increases the chance of making files corrupted.

It should be kept in mind that the SQL Server Database is going to be corrupted one day or another. It is just the matter of when it is going to happen. So it is better to prepare a plan for preventing SQL Server database from corruption.

How to Prevent Corruption in SQL Server database ?

Though it is not practically possible to prevent corruption in SQL Server Database, we need to know how to deal with them to ensure data availability and uptime of data even when corruption occurs. SQL Server even accepts the disk system errors and provides functionality to address corruption when it occurs. Accordingly, with the help of these functions, it is possible to protect against the consequences that corruption might cause. The best way to deal with database corruption in SQL Server is to be able to detect it at the earliest time possible and address it before it is too late.

In some of the cases, it has been found that corruption that has been encountered go unnoticed for long time if the pages or locations has not been queried that were affected by corruption. Let us create a scenario where a database containing sensitive data is in corrupt state. In this database, user writes data almost every day and the data is not queried regularly, as it done only after every 2 months. It is quite possible that the corruption in this database can go unattended for more than a month. If the organization uses rolling backups, it may hamper the ability to address the corruption problem since backups that could have been used to recover from this problem will have been lost. However, if the problem was noticed earlier in just a few days after its occurrence, the corruption issues are likely to be addressed and resolved soon.

Hence, the most important thing that we need to know while dealing with corruption issues in SQL Server Database is to have possibilities to resolve them, which is available only when the corruption is discovered at the early stage. Otherwise, it may become quite difficult to deal with such issues.

Conclusion

In most of the organizations, corruption cases do not only bring risks to data but also bring negative impact to the business flow and revenues. Database Administrators should be able to find ways preventing Corruption in SQL Server. Even if the database is in corrupt state, they should be able to detect the corruption at the earliest time to take required actions for the prevention of permanent data loss. Regular backups needs to be taken, as it provides one and only way to restore data in case of any disaster. The article has been aimed to focus on the corruption in SQL Server Database and approaches preventing corruption in SQL Server in an efficient manner. However, if despite applying all possible fixes one doesn't succeed in preventing SQL Server corruption then going with SQL database recovery software is best to overcome the data loss.

Read More

SQL Log File Corruption and Possible Recovery Methods

Structured Query Language (commonly known as SQL) is a programming language used for managing data held in relational database management system (RDMS) consisting of data definition language, data manipulation language and a data control language. The SQL database comprises of following three files:

1. Primary Database files- Primary database file is the main database file (MDF) which points to another file in database and hence every database has one primary data file. The file is in the extension of .mdf
2. Secondary Database files- When the data of a database exceeds then a secondary database file is created which stores the data that exceeds MDF limit. Hence, multiple secondary data files can be created for a single database. The file is in the extension of .ndf
3. Log files- These files maintains a log of all the transactions done in SQL Server Database so that those information can be later used to recover the database. There must exist one log file for each database and it is possible that more than one log file can be created for a single database. The file is in .ldf file extension.

Log files (also known as transaction log) consist of actions executed on database for database management to guarantee ACID properties over crashes or hardware failure. It is a file listing changes to the database, stored in a stable storage format

Problem

The log files of the SQL server gets corrupted i.e. while the time we are performing some action upon SQL database, their exist some errors in between the process and due to which there is an interrupt in process

Causes of Log File Corruption in SQL Server

• Viruses or other Malicious Software- In computer system, many viruses can infect and damage the log files and makes them inaccessible.
• Terminating System Abnormally- If system/application is quit abnormally, then files are prone to be corrupted or damaged.
• Input Output Configuration- The I/O subsystem is a vital component of database used to store system and user databases. Hence if the configuration is disturbed on enhanced that it may lead to corruption in log files.
• Storage Size Issue- The biggest reason behind the corruption of log files is the storage size. In case the data exceeds the limit of LDF, corruption is likely to occur.

Errors which Occurs Due to Log File Corruption

• Error Message 1: StartLogFiles: The error exists when the log files are unable to start because system could not find the file specified. So try to diagnose and correct the operating system error, and retry the operation.
• Error Message 2: File activation failure. The error occurs due to error in file on location‘C:\ProgramFiles\MSSQLServer\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG’.
• Error Message 3: The error message displays that the transaction log has been deleted or lost due to a hardware failure of system or any other reason. The log files cannot be redeveloped because users perform open transactions on file.
• Error Message 4: Corrupted server of SQL database leads to corruption in backup of log files.
• Error Message 5: When database log is corrupted and user is attempting to attach the log file to new server then an error message displays with a message that ‘Could not open new database (Name of Database). CREATE DATABASE is aborted.
• Error Message 6: When the log database is attempted to attach but it gives an error while performing the attachment. The error displays one of the two number i.e. 9004 or 9001 notifying that you have to create a backup or it is necessary to rebuild the log.

How to Recover Corrupted Log File in SQL Server?

In order to repair a corrupt LDF file, use the WITH TABLOCK option for DBCC CHECKDB. It will recover the data from a corrupted LDF file that has been corrupted or damaged due to some reason such as logical corruption.

Conclusion

Through the above solution, we can recover SQL log file but not sure that the above procedure will recover complete log file data. However, there exists SQL Server transaction log recovery tool through which you can restore database transaction log.

Read More

How to Create an Audit Trigger In SQL Server

Overview

In my previous article, we have discussed about Temporal Tables in SQL Server 2016, and understand much more about it. Now in this article we will discuss about triggers and will know how to create an audit trigger in SQL Server.

In many applications’ database, it is a very difficult task to find who did something and at what time was the activity performed on that application. However, in SQL Server it is easy to find someone’s activity at any time by using some special type of SQL objects like triggers.

What Are Triggers in SQL Server?

In SQL Server, a trigger is a special kind of stored procedure or database object that automatically executes and captures events or actions that a user performs on a SQL Server database. SQL Server comprises of two types of triggers:

1. DML Triggers (Data Manipulation Language)
2. DDL Triggers (Data definition Language)

DML Triggers: DML triggers applies when a user performs operations like INSERT, UPDATE, or DELETE in a specified table or view.

DDL Triggers: DDL triggers applies when a user performs operations such as CREATE, ALTER, and DROP. This kind of trigger is used for administrative tasks like; auditing database operations.

Now, after a small description of triggers in SQL Server we will focus on our main topic that is how to create an audit trigger in SQL Server.

Why Do We Need To Create An Audit Trigger?

The basic need to create an audit trigger in SQL Server is for security purpose and these things become evidence at the time of tampering:

• Client software that was used for accessing data
• Date and time, at which the change was occurring
• Data value, prior and after change

It means that all the above info works as evidence when a trigger is fired.

Now we will create an SQL Server Database audit trigger that will audit and store all the security related events of SQL Server.

USE [master]
GO
DROP TABLE [dbo].[SecurityLog]
GO
CREATE TABLE [dbo].[SecurityLog](
      [EventType][nvarchar](128) NULL, 
      [EventTime][datetime] NULL,
      [EventLog][xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Now we will create DDL trigger that will capture and store all connections and security-related events:

USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_triggers 
             WHERE name = 'ddl_trig_capture_security_events')
DROP TRIGGER ddl_trig_capture_security_events
ON ALL SERVER;
GO

CREATE TRIGGER ddl_trig_capture_security_events
ON ALL SERVER;
FOR  LOGON, DDL_SERVER_SECURITY_EVENTS,        
       DDL_DATABASE_SECURITY_EVENTS
AS    
      INSERT INTO [master]..[SecurityLog] (EventType, EventTime, EventLog)      
     SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(128)')              
     ,EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime')             
,EVENTDATA()
GO

Once you create an audit trigger, you can test it using the following command:

USE [master]
GO
CREATE LOGIN [TestDDL] WITH PASSWORD=N'TestDDL'
GO
USE [AdventureWorks2012]
GO
CREATE USER [TestDDL] FOR LOGIN [TestDDL]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TestDDL]
GO
GRANT EXECUTE ON [dbo].[uspGetBillOfMaterials] TO [TestDDL]
GO

SQL Server database audit trigger for event notification

USE [msdb]
GO
--Creating queue
CREATE QUEUE [SecurityEventsQueue]
GO
--Creating service for the queue
CREATE SERVICE [//AdventureWorks.com/SecurityEventsService]
AUTHORIZATION [dbo]
ON QUEUE [dbo].[SecurityEventsQueue]
GO

Creating route for the service

CREATE ROUTE SecurityEventsRoute
WITH SERVICE_NAME = '//AdventureWorks.com/SecurityEventsService',
ADDRESS = 'LOCAL';
GO

Creating Event Notification to capture connection and secrity-related events

USE [msdb]
GO
CREATE EVENT NOTIFICATION NotifySecurityEvents
ON SERVER
FOR AUDIT_LOGIN,       
       AUDIT_LOGOUT,       
      AUDIT_LOGIN_FAILED,       
      DDL_SERVER_SECURITY_EVENTS,       
      DDL_DATABASE_SECURITY_EVENTS
TO SERVICE '//AdventureWorks.com/SecurityEventsService' ,    
           '9D584F73-1796-4494-ADC2-04BDD729FBCE';
GO

We will create a service program that will process the event messages that are generated via Event Notification objects:

IF EXISTS (SELECT * FROM [sys].[objects] WHERE [name] = 'sProcessSecurityEvents')
DROP PROCEDURE [dbo].[sProcessSecurityEvents]
GO

CREATE PROC [dbo].[sProcessSecurityEvents]
AS BEGIN
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
BEGIN TRY
DECLARE  @message_body [xml]             
             ,@EventTime [datetime]             
            ,@EventType [varchar](128)             
           ,@message_type_name [nvarchar](256)                     ,@dialog [uniqueidentifier]

Endless loop

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;

Receive the next available message

WAITFOR (RECEIVE TOP(1)                    
              @message_type_name = [message_type_name],                    
                 @message_body = [message_body],                    
                 @dialog = [conversation_handle]
FROM [dbo].[SecurityEventsQueue]), TIMEOUT 2000

Rollback and exit if no messages were found

IF (@@ROWCOUNT = 0)
BEGIN       
        ROLLBACK TRANSACTION;       
        BREAK;
END;

End conversation of end dialog message

IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN       
       PRINT 'End Dialog received for dialog # ' + CAST(@dialog as [nvarchar](40));       
       END CONVERSATION @dialog;
       END;
ELSE
BEGIN       
       SET @EventTime = CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS [nvarchar](max)) AS [datetime])       
       SET @EventType = CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS [nvarchar](128))       
       INSERT INTO [master]..[SecurityLog] ([EventType], [EventTime], [EventLog])       
      VALUES (@EventType, @EventTime, @message_body)
END
COMMIT TRANSACTION
END --End of loop
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()             
            ,ERROR_SEVERITY()             
            ,ERROR_STATE()             
            ,ERROR_PROCEDURE()             
            ,ERROR_LINE()             
            ,ERROR_MESSAGE()
END CATCH
END
GO

After successfully created service program, execute the following script to activate service broker queue:

ALTER QUEUE [dbo].[SecurityEventsQueue]        
        WITH STATUS = ON       
        ,ACTIVATION (PROCEDURE_NAME = [sProcessSecurityEvents]       
        ,STATUS = ON       
        ,MAX_QUEUE_READERS = 1       
         ,EXECUTE AS OWNER)
GO

Now test and examine the output of the SecurityLog table.

To track the security-related event we can also use SQL Server audit.

USE [master]
GO
CREATE SERVER AUDIT [Audit-SecurityEvents]
TO FILE
(      FILEPATH = N'D:\Demo_SQLAudit'       
      ,MAXSIZE = 200 MB       
      ,MAX_ROLLOVER_FILES = 2147483647       
       ,RESERVE_DISK_SPACE = OFF )
WITH
(      QUEUE_DELAY = 1000       
          ,ON_FAILURE = CONTINUE )
GO

After this now, we will create an audit specification to audit our SQL server and database security-related events like Login Password change, Database user or object permission change, failed login attempts and server principle change:

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
FOR SERVER AUDIT [Audit-SecurityEvents]
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
GO

Conclusion

In this section, we learnt about how SQL Server uses DML and DDL triggers for security purposes. These DML and DDL triggers capture user events that they performed on SQL Server. Although some SQL features such as SQL Audit are not yet fully developed, auditing of SQL server database using DDL triggers have several disadvantages. For example, a user can easily disable the triggers if he has sufficient permissions. Another disadvantage of using trigger in SQL Server is that triggers perform a schema change and then delete the captured information from auditing repository. In addition, the procedure to create an audit trigger in SQL Server is too long and time consuming. Therefore, it is better to use SQL log viewer tool, which saves a significant amount of valuable time and provides you a convenient result.

Read More

Know About Temporal Tables in SQL Server 2016

Temporal tables were included in SQL Server 2016 as a new feature. These temporal tables are also known as system-versioned tables allow SQL Server for keeping the history of data in table automatically. A temporal table can be defined as a table for which PERIOD definition exists comprising of system columns. These columns are available with data-type of datetme2 where the period of validity is recorded by system. It also has a history table associated with it where all the system records of previous versions are recorded. So the most significant function of Temporal Table is that it allows storing data in table at any point in time. Usually what happens when you execute any delete statement on table, the old data will be overwritten and you will view latest data (after deletion) but with temporal table you can view latest data and also the old data (without deletion). This is because temporal table value of all the entries or records is maintained and at any point can be determined.

Significance of Temporal Tables

ANSI SQL 2011 standard initially introduced the Temporal Tables, which later was released in SQL Server 2016. As mentioned above, this temporal tables or system-versioned tables let users to query updated or deleted data. On the other hand, a normal table would only return the current data from the table. This practical explanation may help: Suppose a column value is updated from 10 to 20, a normal table will retrieve the current value i.e. 20 only. However, temporal table will allow retrieving old value 10 also. This is done by keeping a history table with all the records, which were entered or updated.

Use Cases for Temporal Table:

• Indulgence of changing business trends
• Recording data changes done timely
• Reviewing changes done to the table
• Recover accidental data changes
• Recovering application errors
• Slowly changing dimensions
• Repairing record-level corruption

Temporal tables or system-versioned tables is actually an assertion table, which means that it records the complete details and entries like update or deletion made to the databases based on physical dates. Versioning is not supported by temporal tables, which means the versioning of records because of logical dates. This conduces that the time must be taken into consideration if you want to maintain the logical change like time.

How To Create A Temporal (System-versioned) Table?

Pre-requisites & Limitations:

• A Primary Key has to be defined.
• Two columns for recording start and end date should be defined with data type of datetime2. These columns are referred as SYSTEM_TIME period columns.
• AFTER triggers are allowed but INSTEAD OF triggers are not allowed.
• In-memory OLTP cannot be used.
• Temporal and history table cannot be FILETABLE.
• Statements like INSERT and UPDATE cannot reference the SYSTEM_TIME period columns.
• Data available in history table cannot be changed.

Below mentioned script can be used to create a simple system-versioned table:

When you run this command as query, it will create two tables. One parent Temporal/System-versioned Table, and History table. Here the table with the name dbo.TestTemporal will be created. History table is nested under the parent table and if the name of history table is not specified, SQL Server automatically generates it as dbo.MSSQL_TemporalHistoryFor_objectidoftable.

Closer look at result:

History table possesses an identical set of columns but the constraints are removed. It carries its own set of indexes and statistics. In order to improve the performance, your own indexes can be created.

Conclusion

Temporal tables are significant additional feature of SQL Server 2016. As it will track the history of the records entered in the temporal tables. As the history records are maintained well, it is easy to recover the data at any point instead of only current data recovery. Possible use cases for temporal table can be type 2 dimensions in data warehouses, protection for accidental deletes or updates, auditing, etc.

Read More

Accomplishing a Strategized SQL Server Disaster Recovery

Introduction

The appropriate SQL Server disaster recovery plan means to be prepared for tackling with physical as well as logical cases of database loss to bring the server back into a running state, and not just the recovery of data once it is already been lost. Different types of disasters could range from accidental deletion of data, failure of hard drive, server misconfiguration resulting in failure of deployments, etc. However, when a disaster actually hits it happens to take place in the most instant and unfamiliar ways causing the administrators to take unskilled actions against it, resulting in the worsening of the situation.

Dealing With Master MDF File Corruption

On the installation of SQL Server, five different types of system database files are automatically created, namely; Resource (in 2005 and above versions of Server), master database, model, tempdb, and msdb. Corruption of master.mdf usually results in a catastrophic loss regaining which either requires restoration or rebuilding of the database entirely. Users highly rely upon reinstalling the entire server from the scratch in case of such disasters, as part of their recovery plan. Instead, the rebuilding of database followed by its restoration from the last best known backup is considered more reliable and solution generative. However, rebuilding is known to be a kind of sluggish procedure that may take up a lifetime causing a lot of time being consumed in trial and error method only, mostly when performed on a cluster.

Recovery of Backup Meant for Database Restoration

Backups are not the only escape from database related disasters and not even the ultimate way to attain continuity. Though, backups have proven to be the foremost choice of administrators to bring back lost database to its full-fledged state. Microsoft offers a variety of backup options, for SQL Server database, to choose from wherein the size of storage done by server determines the right plan for its backup. However, the mere existence of a backup on the machine doesn’t necessarily imply its usability. An existing backup if not tested for restoration process since creation could possibly be troublesome at the very time of disaster as things will take place haphazardly.

How to restore the database, which component to be restored or how to restore all components – and many such baffling queries tend to arise at the time of performing backup restoration. Thus, regular or at least alternate testing ensures whether the backup is in a state of restoration or not. As at the end if you find backups to be corrupted at the time of requirement, then they have failed the very purpose of existence itself. And you can

Restoration of Database through Transaction Log

Transaction logs maintain the history of tasks performed on a database, whether a query inserted, dropped, or so on. Being an integral part of each database on the SQL Server, it also holds a lot of importance in the scenario of disaster recovery, however; the only condition is that the log must not be in a corrupt state to come in use for the respective purposes. Every modification that takes place in the database is logged into the transaction in the form of a log record in a sequential manner. In case of a system failure, bringing a database back into a consistent state the transaction log file is required. Unless the complexities and consequences of deleting or moving a transaction log file for an essential database is known and understood, the action must not be taken.

Accessing SQL Database During Server Downtime

The term disaster recovery was coined on the basis of maintaining server and database continuity despite disasters causing physical damage to the server resulting in compromise with database. Like any high end servers, SQL Server administrators are usually prepared with backups and disaster management plans to deal with the consequences. However, backups play no role in bringing back business continuity when the server is in downtime leaving the database in an orphaned state. On studying the cases of SQL Server downtime, applying patches of service packs majorly resulted in the end of server operation. Moreover, cases like unknown technical glitches like; server crash, bug infection, administration error, and more have resulted in the necessity of maintenance. Therefore, the server database even in a healthy state remains to be of no use when the server itself is not in an up and running state to support its accessibility.

Solution: Server administrators employ many different workarounds in order to keep server operation and database integrity maintained. Therefore, making the tasks more cumbersome rather than streamlining it. Master database backup, log backup, server maintenance without compromising operations, and much more has to be done by the admins in order to achieve optimal disaster recovery. A variety of commercial solutions in the market offer rather more helpful ways to deal with SQL Server and its storage related disaster, compared to the standard methods employed.

Conclusion

SQL Server Recovery Manager serves a unified platform for performing most of the SQL Serve disaster recovery serving procedures, including; database recovery, backup recovery, and standalone access of database (without SQL Server), usage of transaction log entries for relatively quicker database restoration, decryption of database, and more. The application has distinguished modules to serve each of the defined purposes and is purchasable under respective user based licenses. However, a demonstration version of the same makes it possible for the application to be tested thoroughly for its potential.

Read More

Fix login issues in SQL server using SQL Server Authentication

Introduction

For the security of user’s database, SQL server provides some authentication modes by which an unauthorized person cannot access user’s database. In this section we will know about the types of authentication modes provided in SQL server and learn how to deal with login failure issues on SQL server.

At first we will discuss how to configure the authentication mode in SQL server.

Authentication Mode Configuration

The SQL server provides two types of Authentication modes during the setup. One is Windows Authentication Mode and second is SQL Server Authentication Mode. It is important to choose one of these modes for the database engine. The Windows Authentication Mode allows you to connect through Windows NT 4.0 or Windows 2000 user account and disable you for SQL Server Authentication while the mixed mode allows you for both Windows Authentication and SQL Server Authentication. The Windows Authentication is always accessible for the user.

Let us know what happens when we use Windows Authentication Mode.

Windows Authentication Mode

When you use Windows authentication mode, the SQL server confirms the user name and password through the windows principal token in the operating System, which means that the windows confirms the user identity. The SQL server doesn’t inquire for any identity validation. The Windows authentication mode is more secure than SQL Server Authentication mode because it is a default authentication mode.

Security Features of Windows Authentication:

1. Uses Kerberos security protocol
2. Provides validation complexity for a strong password
3. Supports account lockout after attempting multiple invalid logins
4. Manages password expiration

A Windows authentication connection is more secure than a SQL server authentication. Because the maintenance of users and groups are only managed by Windows 2000 or Windows NT 4.0

NOTE: Windows Authentication Mode is useful when SQL Server runs only on Windows 2000 or Windows NT 4.0.

SQL Server Authentication Mode

In SQL server authentication mode logins are created on the basis of SQL server. The user name and password are built in SQL server and stored in SQL server. When using SQL server authentication mode, you need to create strong passwords for all the SQL server accounts. The SQL server authentication mode permits SQL server to support applications, which is provided by third parties and expects SQL server authentication. It also permits the SQL server to use web based applications by which users create their own identities. But it cannot use Kerberos security protocol. We can use the SQL server authentication mode when we run SQL server on Windows 98 because Windows 98 doesn’t support Windows Authentication mode.

NOTE: In SQL server authentication mode the username and passwords are saved in a SQL server’s master database and when we use SQL server logins, the username and passwords are passed through a network, which makes them unprotected.

When you install SQL server, it installs with a login named SA (System administrator). You need to create a strong password for the SA.

To enable SA login on SQL server follow the simple steps given below:

1. Click on Object Explorer>> click on Security>> click on Logins
2. Right click on SA>> click on Properties
3. You need to create and confirm a password for the login on the General page
4. On the Status page>> in the Login section>> click Enabled>> then click Ok

What If I Can’t Access SQL Server & Keep Getting Error 18456?

There are a number of reasons, from which the login failed error 18456 can occur. Sometimes the error 18456 displayed that the server name is correct, but the connection is not established due to a number of reasons. The error is same for all other versions of SQL server. The error message 18456 does not show all the information regarding to the error, rather it hides all the information from unauthorized users in case someone tries to misuse the SQL server. But you can check the detail of this error 18456 if you are the administrator of SQL server.

Causes and Troubleshooting of Error 18456

1. Unrecognized User: The main reason of this error is that the mixed mode authentication is enabled and you are trying to login with Windows Authentication with the user who is not recognized by SQL server. This error may occur while you are using user account control on Windows 7 or Windows Vista. You should use “Run as Admin Option” if you are using Windows 7 or Windows Vista.

2. Incorrect Password: The second reason could be that you are using the SQL server authentication and the password may be incorrect or maybe expired.

3. Disabled Authentications: One other cause of this error is that the SQL server authentication or Windows Authentication is not enabled by SQL server itself.

Sometimes, it happens when the sysadmin had changed the password of username SA and now he is not able to recall the password. In such case you need to reset SA password.

How to Reset SA Password?

SQL server provides you some facilities to reset your SA password.

You can reset your SQL server password by using the other SQL server account who is the member of the sysadmin role

Follow the below steps to reset SA password:

1. Go to Object Explorer >> open Security Folder >> open Login folder
2. Then right click on SA Account >> go to Properties
3. Change SA password >> then click OK

NOTE: To check the new password, restart the SQL server and all its other services then test the new password.

NOTE: It is important to turn off the single user mode in SQL Server before attempting above procedure.

Conclusion:

In this article we learned that how many authentication modes we are using in SQL server and how we use them. Also, did we learn about error 18456 and how do we reset the SA password because the SA password in SQL server provides much security. But unfortunately, if we forget the SA password or if we are facing any other issue related to SA password, because of reasons whatsoever, from which the login failed error 18456 can occur there is no way out. So in such case you need to have an SQL SA Password Recovery Tool with which you can regain access to your database and also secure it.

Read More

Read the Transaction Logs in SQL Server

INTRODUCTION

In this tutorial we will know about, how to read a transaction log in SQL server and also we will learn all the reading methods of a transaction log. From this tutorial you will know why MS SQL permits you to read the transaction logs. As we know very well about the transaction log, but for a consideration, I will give you a small introduction of transaction log, thereafter we will further discuss about it.

What is Transaction Log in SQL Server?

The Transaction log is a technique to keep your records secure in SQL server. The transaction log is a kind of backup for records through which, we can recover our database from a disaster. Also the transaction log is used to identify those records which are mistakenly deleted by the user. Without the transaction log a user cannot perform any task in SQL server.

From this small introduction, we learned how much a transaction log is important for us. So, now coming over to the topic, let us know more about transaction log.

What Events Captured In The Transaction Log?

Except some events, the transaction log captures all the important events like:

• Begin or End the transaction.
• All the insert, delete and update events.
• Creation of indexes and tables
• Drop events.
• All the page allocation and De-allocations.
• All the locks and truncation of tables.

These above events are captured in the transaction log but there are also some events such as select into, bulk insert bcp..etc. These logged minimally and depend on recovery model of the database.

Why Is It Important To Capture A Transaction?

As we know that the database is quite vulnerable to corruption. As the data is critically important for the user, it is equally important for the database. So keeping in mind that the database goes into a stable state where we cannot recover it, the SQL server captures the transaction to recover the data so, it can be used as a backup, if necessary.

The second reason is that, if a user accidentally deletes a database, the captured transaction can be used to recover the deleted data.

Note: Although the recovery of the data totally depends on recovery models and also depends on user, i.e. which recovery model has been fixed for the database.

How to Read A Transaction Log In SQL Server?

Microsoft doesn’t provide any tool to read the transaction logs, but it does provide some undocumented functions using which a user can read all the transaction logs. But Microsoft doesn’t support these undocumented functions, rather it gives a warning: “use these functions at your own risk”.

Here are some problems in fn_db_dumplog. Microsoft’s SQL team is aware of this problem and will fix it in future. These undocumented functions show what transaction logs captures. Here is an undocumented function fn_dblog, which helps you to read the transaction logs.

Let’s see how an undocumented fn_dblog function works in SQL server.

Reading Transactions using fn_dblog function

Following steps tells, how we can read the transaction logs in SQL server:

1. Create database readtranlog;
        GO

In the first step I have created a database & named it readtranlog.

2. Use readtranlog;
        GO
        Create table persons
        (sn int IDENTITY,
        Name CHAR (25)
        );

After that in second step I have created a table named persons.

3. Use readtranlog;
        GO
        Select count(*) from fn_dblog(null,null)

In the third step we can check what steps and processes have been used by the SQL server to create both; the database and table.

We can see that there are number of processes that have been generated for just creating a database and a table.

4. Use readtranlog;
        GO
        select [current lsn],
        [operation],
        [transaction name],
        [transaction id],
        [transaction sid],
        [spid],
        [begin time]
        From  fn_dblog(null,null)

In the fourth step i have shown a few records but we can see what data is available in the transaction log file. There are some important columns (Transaction Name) in the fourth step, which shows the database name as well as the table name. The [Transaction Id] is same for all the [Current LSN].

5. Use readtranlog
        GO
        Insert into persons values (1, ‘mike’);
        GO 
        Update persons
        Set Name =’peter’
        Where Name =’mike’
        GO
        Delete persons
        Where sn=’1’
        GO

In the fifth step we perform insert, update and delete operations to check how these transactions are logged in the database log file.

6. USE readtranlog;
        GO
        SELECT
        [current lsn],
        [transaction id],
        [operation],
        [transaction name],
        [context],
        [allocunitname],
        [page id],
        [slot id],
        [begin time],
        [end time],
        [number of locks],
        [lock Information]
        from sys.fn_dblog(NULL,NULL)
        where operation IN 
      ('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
   'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')

In last, sixth step, it shows that how these three transactions are logged in the transaction log file.

Conclusion

The above six steps illustrate how we can use the fn_dblog function to read a transaction log in SQL server. We can detect those transactions which were accidentally deleted and also detect those transactions which we do not know about. But it is a too long and time consuming process and the fn_dblog function can only read and show the log data, but it cannot decode the log data so it is difficult to read the output of fn_dblog function. Because Microsoft does not deliver any log reader tools so it is good to have an SQL log reader tool by which transaction logs can easily be read and also be used for forensics.

Read More