Tuesday, May 24, 2016

Database Restore Fails with Msg 3154

Have you ever tried to restore over an existing database only to receive the following error message?

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'AdventureWorks2012' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

We could easily avoid this error by using the WITH REPLACE option in the RESTORE DATABASE command, but we want to know why this error occurs and how SQL Server knows they are not from the same family. The answer is the database_guid.

The database_guid is a GUID that is unique to every database; even across servers. If you restore a backup to another server, the restored database will get a new database_guid. You can view this value for every database by looking in the catalog view sys.database_recovery_status.

SELECT DB_NAME(database_id), database_guid
FROM master.sys.database_recovery_status;


If the database_guid is different for every database regardless of which SQL Server it’s on, then how does the restore still know the backup and database are from two different families? The answer is the family_guid.

The family_guid is the original database_guid when the database was first created, and does not change for the life of that database. When you issue a RESTORE DATABASE command, SQL Server will compare the family_guid to the database_guid if you are attempting to restore over an existing database. When those numbers don’t match, you will receive that error. You can get the family_guid from the same catalog view.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM master.sys.database_recovery_status


You can use the following code to pull the family_guid from the backup and then compare it to the database you are trying to overwrite.

-- Build a temp table to store the backup file header information.
IF OBJECT_ID('tempdb..#BackupHeader') IS NOT NULL
    DROP TABLE #BackupHeader;
GO

CREATE TABLE #BackupHeader (
     BackupName nvarchar(128)
    ,BackupDescription nvarchar(255)
    ,BackupType smallint
    ,ExpirationDate datetime
    ,Compressed bit
    ,Position smallint
    ,DeviceType tinyint
    ,UserName nvarchar(128)
    ,ServerName nvarchar(128)
    ,DatabaseName nvarchar(128)
    ,DatabaseVersion int
    ,DatabaseCreationDate datetime
    ,BackupSize numeric(20,0)
    ,FirstLSN numeric(25,0)
    ,LastLSN numeric(25,0)
    ,CheckpointLSN numeric(25,0)
    ,DatabaseBackupLSN numeric(25,0)
    ,BackupStartDate datetime
    ,BackupFinishDate datetime
    ,SortOrder smallint
    ,CodePage smallint
    ,UnicodeLocaleId int
    ,UnicodeComparisonStyle int
    ,CompatibilityLevel tinyint
    ,SoftwareVendorId int
    ,SoftwareVersionMajor int
    ,SoftwareVersionMinor int
    ,SoftwareVersionBuild int
    ,MachineName nvarchar(128)
    ,Flags int
    ,BindingID uniqueidentifier
    ,RecoveryForkID uniqueidentifier
    ,Collation nvarchar(128)
    ,FamilyGUID uniqueidentifier
    ,HasBulkLoggedData bit
    ,IsSnapshot bit
    ,IsReadOnly bit
    ,IsSingleUser bit
    ,HasBackupChecksums bit
    ,IsDamaged bit
    ,BeginsLogChain bit
    ,HasIncompleteMetaData bit
    ,IsForceOffline bit
    ,IsCopyOnly bit
    ,FirstRecoveryForkID uniqueidentifier
    ,ForkPointLSN numeric(25,0) NULL
    ,RecoveryModel nvarchar(60)
    ,DifferentialBaseLSN numeric(25,0) NULL
    ,DifferentialBaseGUID uniqueidentifier
    ,BackupTypeDescription nvarchar(60)
    ,BackupSetGUID uniqueidentifier NULL
    ,CompressedBackupSize bigint
    ,containment tinyint NOT NULL
);

--SQL Server 2014/2016 have three extra columns in the file header.
IF (@@MICROSOFTVERSION/POWER(2,24) > 11)
ALTER TABLE #BackupHeader
ADD  KeyAlgorithm nvarchar(32)
    ,EncryptorThumbprint varbinary(20)
    ,EncryptorType nvarchar(32);
GO

-- Insert file header info into the temp table.
INSERT #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK = ''C:\SQL_Backup\AdventureWorks2012.bak''');
GO

-- Compare the family_guid values.
SELECT DatabaseName,FamilyGUID FROM #BackupHeader;
GO

SELECT DB_NAME(database_id),family_guid FROM sys.database_recovery_status
WHERE database_id = DB_ID('AdventureWorks2012');
GO


As you can see they do not match, which is why we get the error.

BONUS INFO:
There is one case where the database_guid can be the same even for different databases. If you detach a database, make a copy of the MDF and LDF files, and then re-attach those files as a different database name, the database_guid values will be the same.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM sys.database_recovery_status;


As you can see, the database_guid and family_guid are the same for all three copies of the AdventureWorks database.

Tuesday, November 17, 2015

Create a Whitelist for SQL Server

In my previous post, Configuring the Azure SQL Database Firewall, I discussed how you can configure the firewall rules to manage access to the SQL Server. Today, I wanted to demonstrate how you can accomplish the same thing with your on-premise SQL Servers by creating a whitelist.

So let’s start off by defining “whitelist”. According to the American Heritage Dictionary, it is “a list of people or organizations that have been approved to receive special considerations”. In our example, that “special consideration” is access to the SQL Server only if your workstation IP address is on the whitelist.

So why would we care about this? Why not just manage access to SQL Server the normal way with logins and passwords. Here is one example. Let’s say you have a company policy that prohibits anyone from using a common login to connect to a SQL Server. But your application uses a single SQL login to make its connection to SQL Server, and EVERY developer in the company knows the password. Even though there is a written policy in place, what would prevent one of those developers form connecting to SQL Server to fix a bug, or worse, change data to circumvent the application logic.

A whitelist will define which logins are allowed to connect to SQL Server from a specific IP address. Using our scenario from above, we can walk through an example. First we need to create the whitelist table to enforce this policy: the application login (WebSiteLogin) should only be allowed to connect to SQL Server if it originates from the web server’s hostname (webserver1) and IP address: 192.168.100.55.

USE master;
GO

IF OBJECT_ID('dbo.WhiteList') IS NOT NULL
    DROP TABLE dbo.WhiteList;
GO

CREATE TABLE dbo.WhiteList
(
     Id INT IDENTITY(1,1) PRIMARY KEY
    ,LoginName VARCHAR(255)
    ,HostName VARCHAR(255)
    ,HostIpAddress VARCHAR(15)
);
GO

Next, we need to add an entry to allow access.

INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress)
SELECT 'WebSiteLogin','webserver1','192.168.100.55';
GO

Just creating the whitelist does nothing. That’s why we need to use a logon trigger to enforce the whiltelist rules.

CREATE TRIGGER WhiteListTrigger
ON ALL SERVER FOR LOGON
AS
BEGIN
    DECLARE 
         @LoginName varchar(255) = ORIGINAL_LOGIN()
        ,@HostName varchar(255) = HOST_NAME()
        ,@HostIpAddress varchar(15);

    SELECT @HostIpAddress = client_net_address 
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID;

    IF 
    (
        SELECT COUNT(*) FROM dbo.WhiteList
        WHERE 
        (
            (LoginName = @LoginName) OR (LoginName = '*')
        )
        AND
        (
            (HostName = @HostName) OR (HostName = '*')
        )
        AND
        (
            (HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*')
        )
    ) = 0
    ROLLBACK;
END;
GO

The logon trigger simply compares the user’s login name, hostname, and IP address to what’s in the WhiteList table. If there is a match, then access is allowed. If not, then the connection is terminated via a rollback. To test this, we can attempt to login using WebSiteLogin from an IP address other than 192.168.100.55.


Immediately, we are greeted with a logon error message.


In addition to comparing the logins, the code also adds a wildcard option using the asterisk (*).

SELECT * FROM dbo.WhiteList;
GO


Line 1 has an asterisk for LoginName and HostIpAddress. This means that any login from any IP address is allowed to connect as long as the HostName is dbserver. Line 2 has an asterisk for HostName and HostIpAddress. This means the sa login is allowed to connect from any hostname or IP address. Line 3 is the example we used above. An asktrisk in all three columns would allow any login from any hostname or IP address to connect.

In the event you lock yourself (or everyone) out of the SQL Server, there is a quick way to restore access. You’ll need to connect to SQL Server using the Dedicated Admin Connection, either through Management Studio or the SQLCMD command line using a login with SysAdmin permission. Once connected, you can disable the logon trigger.


From the example above, you can see how quickly you can implement your own firewall for your on-premise SQL Servers using a whitelist and a logon trigger.

Additional resources about Logon Triggers:
https://msdn.microsoft.com/en-us/library/bb326598.aspx

Tuesday, November 3, 2015

Configuring the Azure SQL Database Firewall

Azure SQL Database is a Platform as a Service (PaaS) that provides a relational database for use over the internet. That sounds super cool and easy to use. But wait, there’s one word I’d like to highlight in that first sentence: “internet”. Anyone with an internet connection could access your database. Now that’s no cool. So how does Microsoft keep your database safe? The answer is a multipronged approach of using encryption, authentication, authorization, and firewalls.

Continue reading...

Friday, October 16, 2015

A New Achievement

To celebrate a recent achievement, I have decided to create a whole new section to my blog that will be dedicated to the subject. Please follow the link to learn more.

Continue reading...

Wednesday, October 14, 2015

Speaking at SQL Saturday #452 - Charlotte, NC - October 17

In case you missed it last week, there will be another SQL Saturday this weekend in Charlotte, NC. If you are in the area, please come out to this free training event to hear a bunch of great professionals talk about SQL Server. You can register for the event here: http://www.sqlsaturday.com/452/eventhome.aspx.

I will be presenting one session on Performance Monitoring Tools.

No Money for Performance Monitoring Tools? No Problem!
So you like the idea of using one of the commercially available performance monitoring tools but can’t convince your boss to spend the money? Then join me as we explore some absolutely free tools born right out of Microsoft’s customer support teams: DiagManager and SQLNexus. We will see just how easy it is to use them to collect and analyze performance data from your SQL Servers. These tools can get you started quickly without having to deal with the red tape.