Introduction to Transparent Data Encryption (TDE)

1. Introduced in SQL Server 2008 version.

2. It performs real-time I/O encryption and decryption of a SQL Server database's data and log files.

3. Use AES, 3DES encryption algorithm.

4. No application changes are required to take advantage of TDE.

5. Backup of database protected by TDE are also protected.

6. Encrypted data compresses significantly less than equivalent unencrypted data. Therefore, using TDE and backup compression together is not recommended.

7. TDE is available in SQL Server 2012 Enterprise edition not available in Standard and Business Intelligence edition. TDE available in SQL Server 2008 and SQL 2008 R2 Data Center and Enterprise editions.

8. TDE protect data "at rest" meaning the data and log files, but authorized user such as security administrator can access the data in a TDE-encrytped database. To prevent an SA or DBA from accessing selected parts of the data you need to use application level encryption.

9. Some performance overhead is involved in using TDE.

10. TDE encrypts data stored on disk at the page and log level. The data that sent across the wire to the client connection isn't encrypted.

11. If you want to encrypt the link between SQL Server and client system, then need to use SSL (Secure Sockets Layer).

12. TDE does not increase the size of the encrypted database.

13. To use TDE, follow these steps.

a. Create a master key
b. Create or obtain a certificate protected by the master key
c. Create a database encryption key and protect it by the certificate
d. Set the database to use encryption

14. TDE encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module.

15. The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE.

16. Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted intermediate files; for example, the bcp files.  During transactional or merge replication, encryption can be enabled to protect the communication channel.

17. FILESTREAM data is not encrypted even when TDE is enabled.

How to identify and fix the Orphaned Users in SQL Server?

Who are Orphan User?

An user in a database is called orphaned when the associated login does not exists in the server or because the SID of the login and user in the database is not matching. You can check this by examining the catalog views  sys.server_principals and sys.database_principals.

Scenarios

  1. When you delete a login from the server.
  2. When you restore the database from one environment (production instance) to  another (development instance).
Identifying the orphaned users

To keep the database environment clean and safe, it is important to delete/fix the orphan users. In general, the orphaned users created by the first scenario (login deleted) should be deleted and orphaned users created by second scenario should be fixed. The easiest way to identify the orphaned users is, use the system procedure sp_change_users_login with report option as given below.


EXEC SP_CHANGE_USER_LOGIN 'Report'

But interestingly, this procedure will list only the orphaned users associated with the SQL login. If the login associated with windows login, the above procedure will not return the desired result.

Below script will list the orphaned users from the all databases of an instance.


DECLARE @statement1 VARCHAR(MAX)
DECLARE @statement2 VARCHAR(MAX)
IF EXISTS (SELECT object_id FROM tempdb.sys.objects WHERE name like '#OUsers%')

DROP TABLE #OUsers
CREATE TABLE #OUsers
      (
 UserName VARCHAR(50) NULL,
        userSID  VARBINARY(85) NULL,
        dbName  VARCHAR(50) NULL       
      )  ON [PRIMARY]
SET @statement2 = ' sp_change_users_login ''''Report'''''
SET @statement1 = 'USE [?] 
    INSERT INTO #OUsers(UserName,userSID)
    exec ('''+@statement2+''')
    UPDATE #OUsers SET dbName = ''?''
    WHERE DBName is NUll'
EXEC sp_MSforeachdb @command1=@statement1

SET @statement1 ='USE [?] ;
insert into #OUsers
select name ,sid,''?'' from sys.database_principals where sid not in 
(select sid from sys.server_principals ) and type<>''r''
and type_desc=''WINDOWS_USER'''
EXEC sp_MSforeachdb @command1=@statement1

select * from #OUsers


Fixing the Orphaned users

In the scenario where the login is got deleted , the orphan users can be fixed by dropping the user from the databases using the below script.

DROP USER <username>

Some time the above script may throw an error saying that the users owns a schema. Find out the schema owned by this login by querying the catalog view sys.schemas and mapping the principal_id to sys.database_principals. Either drop these schemas or change the ownership based on your environment. To change the ownership of the schema, use the below command

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO NewUserName;

In the scenario where  database restored in a different environment, we can fix the SID mismatch between the sys.server_principals and sys.database_principals by using the system stored procedure sp_change_users_login

If need to link the SIDs of the users to the SIDs of the valid logins in the master database

Exec sp_change_users_login 'update_one''username''loginname'

Or if you are sure that SQL Server login name is the same as the orphaned user of database being mapped, then you may use a shorter command such as this for username.

EXEC sp_change_users_login 'Auto_Fix', 'username'

If a login name does not exists, you would have to create it first before doing the mapping. A quick way to do this is to use the following command which will create the login and then map the login to the user.

EXEC sp_change_users_login 'Auto_Fix', 'username', null,'pwd'

Note: This is a deprecated feature in SQL server 2008 and you can use alter user as given below

You need to first add the login using the below command 

sp_addlogin 'loginname', 'password'

then associate the user with the login you the command.

ALTER USER TestWindowsUser WITH LOGIN [Mydomain\Lastname.Firstname]

This will resolve the Orphan Users issues.

The operating system returned the error '5(Access is denied.)'

Error Message


The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\RATING_DEV.mdf' at 'container.cpp'(2731).

This issue is due to the insufficient permission to the account configured to run SQL Server Database Engine on file system access to the location where database files are stored. 

Beginning with SQL Server 2012 permissions are assigned to the per-service SID for each of its services. If you configured the SQL Server Database Engine service to run on account "NT SERVICE\MSSQLSERVER". The permission to the default location is configured during setup. If you place your database files in a different location, you might need to follow these steps to grant the Database Engine the full control permission to that location.

To grant file system permission to the per-service SID

  1. Using Windows Explorer, navigate to the file system location where the database files are stored. Right-click the file system folder, and then clickProperties.
  2. On the Security tab, click Edit, and then Add.
  3. In the Select Users, Computer, Service Account, or Groups dialog box, click Locations, at the top of the location list, select your computer name, and then click OK.
  4. In the Enter the object names to select box, type the name of the per-service SID listed in the Books Online topic Configure Windows Service Accounts and Permissions. (For the Database Engine per service SID, use NT SERVICE\MSSQLSERVER for a default instance, or NT SERVICE\MSSQL$InstanceName for a named instance.)
  5. Click Check Names to validate the entry. The validation often fails, and might advise you that the name was not found. When you click OK, a Multiple Names Found dialog box appears.
  6. Now select the per-service SID, either MSSQLSERVER or NT SERVICE\MSSQL$InstanceName, and then click OK.
  7. Click OK again to return to the Permissions dialog box.
  8. In the Group or user names box, select the per-service SID, and then in the Permissions for <name> box, select the Allow check box for Full control.
  9. Click Apply, and then click OK twice to exit.
For more details click HERE.

SQL Server Database Version Numbers

A database created by a more recent version of SQL Server cannot be attached or restored to an earlier version of SQL server. This restriction is simply because an older version cannot know about file format changes that were introduced in the newer release.

If you attempt to attach a database to an earlier version, you will get SQL Server ERROR 948 with the internal version numbers listed in the error message text.

For example, the following error occurs if you try to attach a SQL Server 2008 R2 database to a SQL Server 2008 server:

The database 'MyTest' cannot be opened because it is version 665. This server supports version 661 and earlier. A downgrade path is not supported.

Sample text from SQL Server error 948
The cryptic version numbers in the error message refer to the internal database version. These internal version numbers are undocumented but are (at least currently) the same value reported by the DATABASEPROPERTYEX function 'Version' property of the source database.

The table below maps the internal version numbers to SQL Server versions so you can determine the minimum version you need for the attach to succeed:

SQL Server Version Internal Database Version Database compatibility Level
SQL Server 2014 782 120
SQL Server 2012 706 110
SQL Server 2008 R2 660 / 661 100
SQL Server 2008 655 100
SQL Server 2005 SP2+ with VarDecimal enabled 612 90
SQL Server 2005 611 90
SQL Server 2000 539 80
SQL Server 7.0 515 70
SQL Server 6.5 * 65
SQL Server 6.0 * 60