Cannot access the SQL server database using Windows Authentication (on a domain)once BitLocker ON

Issue: Client are trying to use Bitlocker to secure an SQL 2012 Standard installation, but whenever they turn BitLocker ON, they cannot access the database using Windows Authentication (on a domain).
Workaround: You need to use the SQL server service account to enable Bitlocker and initialize the TPM

Installation and initialization

BitLocker is installed automatically as part of the operating system installation. However, BitLocker is not enabled until it is turned on by using the BitLocker setup wizard, which can be accessed from either the Control Panel or by right-clicking the drive in Windows Explorer.

At any time after installation and initial operating system setup, the system administrator can use the BitLocker setup wizard to initialize BitLocker. There are two steps in the initialization process:

1. On computers that have a TPM, initialize the TPM by using the TPM Initialization Wizard, the BitLocker Drive Encryption item in Control Panel, or by running a script designed to initialize it.

2. Set up BitLocker. Access the BitLocker setup wizard from the Control Panel, which guides you through setup and presents advanced authentication options.

When a SQL service account initializes BitLocker, the administrator should also create a recovery password or a recovery key. Without a recovery key or recovery password, all data on the encrypted drive may be inaccessible and unrecoverable if there is a problem with the BitLocker-protected drive.

Reference link:

Best Methods to Encrypt Functions and Stored Procedures.: New blog doc : SQL server Administration

Best Methods to Encrypt Functions and Stored Procedures.

Using Signatures on Procedures to Grant Permissions

A new—and, in my experience, little known—feature in SQL Server 2005 is the use of a signing certificate to sign stored procedures, assemblies, views, and functions. Administrators can assign permissions to the signing certificate itself and can then be sure that the permissions granted cannot be inadvertently modified by changing the stored procedure, assembly, and so on. Signed executable code cannot be changed without breaking the signature, which consequently invalidates any permissions granted to the signature—that is, unless the changed procedure, assembly, view, or function is re-signed with that signature.

The syntax for adding a signature is simple and includes the ability to add an existing signature (presumably created on another server) with a certificate containing only a public key and to specify a password if the certificate is password protected:


[{ WITH PASSWORD = ‘password’ | WITH SIGNATURE=binary_signature}]

The need for signing code may seem rather exotic. In most production environments, administrators do not need to worry about someone other than administrators changing procedures. However, there are important scenarios where this could be very useful:

• A signature enables a low-privileged account to execute a specific, pre-approved task that the account would not otherwise have permissions to perform.

• ISVs may find signing certificates useful for ensuring that the stored procedures, assemblies, and so on, that are released with their product are not changed.

• Signatures solve a difficult problem with cross-database authentication.

The ability to deploy signed stored procedures, assemblies, functions, and views can allow an ISV to effectively prevent code tampering by a customer’s database administrator. This can help prevent local changes that would otherwise become support issues. Signed procedures are easy to include in any deployment scenario: the signatures can be backed up and restored, attached, or scripted as a blob with the ADD SIGNATURE Transact-SQL statement. Technically, a system administrator may be able to temporarily work around this by implementing his or her own certificate of the same name and signing all of the same procedures, but this would not be trivial. It would also be detectable by the ISV.

The real power of signatures lies in cases where data for a particular query is spread among multiple databases on the server instance and there are different security models (or database owners) among the databases.

One way to address this is to ensure that the server logins are mapped as users in both databases and that the users have valid permissions in both databases. Every user granted permission to execute a query in the first database must also be granted appropriate permissions in the second database. Needless to say, this can quickly become unsupportable, especially when the databases are owned by different groups.

Reference article:

How we can use Varchar in encryption column

This method is little different here we have to work with View and will send the one more method like encryption by stored procedure technique. Below I have mentioned the total demonstration.



The goal of the encryption process is to abstract the tables with cell level encryption by over-laying them with views.  This allows the application (with the proper permissions) to continue to work with the encrypted data seamlessly via the normal development methodologies without having to consistently write the code to decrypt / encrypt the columns (cells).  This process is the generally the same for new applications and existing applications that need to be modified to encrypt data with the key difference being the requirement to open the keys for new applications to provide additional security.


The SQL Server encryption functions return varbinary data, so all columns to be encrypted need to be changed to varbinary.  The use of Views to over-lay the tables provides an abstraction layer so the data types can be properly viewed for data modelers, developers, administrators, analysts and other end users.  To encrypt a SSN, varchar(9) field requires the column in the base table to be implemented as varbinary(200). 


Users that access the views, who do not have permissions to open the proper key(s) will not receive an error, but the column will not be decrypted and will be populated with nulls.


Database developers will need to work closely with the data modeling team and the administrators to ensure the data types, views, encryption keys, backup and restore of keys are implemented properly.  Data is not recoverable if the backup of keys and databases are not implemented properly.


Guidelines for implementing cell level encryption.


All tables that include an encrypted cell (column) follow the normal standard naming conventions with the addition of an underscore and the word base to the table name {table name}_base.  All columns follow the standard implementation with the exception of those to be encrypted, which must use a varbinary data type.  The length of the varbinary column is determined by the max length returned from the encryption function. 


A view is created that follows the normal standards, except the name is implemented off the base table by dropping the underscore and the word base that was used to name the table (this allows legacy code to continue to work).  The view will make use of the decryption function and the proper Cast and Convert statements to manipulate the varbinary column to the correct un-encrypted data type.


Inserts, updates and deletes are handled in the normal manner, with the tsql statements being executed against the view, NOT the underlying tables.  “Instead of Triggers” are created on each view to handle the Inserts, updates and deletes.  This provides an additional layer of abstraction so that consistency can be maintained with the code.


A short tsql script example:


–Database Creation


Create database encrypt_test;





use encrypt_test;




  –Created masterkey and Certificate


Create master key encryption by password = ‘knights12$gzmlauncher#1@%dmissionisclear*()’




Create certificate cert_sk with subject = ‘Certificate for accessing symmteric keys – for use by App’


–SQL SERVER can use the following algorithms in encryption:DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 AND AES_256.




  CREATE SYMMETRIC KEY sk_encrypt_test






  —Table Creation


create table dbo.Client_Base


      ( client_Id int Identity(1,1) primary key,


            ssn varbinary(200),


            Amount_due varbinary(200),


            Comments varchar(1000)






  –Created View with a convert clause so we can use


create view dbo.Client




      Select Client_ID,




                  Null,SSN,1,convert(varchar(10),client_Id))) AS SSN,




                  Null,Amount_Due,1,convert(varchar(10),client_Id)))) AS Amount_Due,




      From dbo.Client_Base;






select * from dbo.client;




—Created Insert Trigger


create trigger dbo.trg_client_insert

      on dbo.Client










            Declare @Client_ID int, @SSN varchar(9), @Amount_Due money,


                  @Comments varchar(1000);


            open SYMMETRIC key sk_encrypt_test decryption by certificate cert_sk;


            DECLARE cur_Client CURSOR FOR

                  SELECT      SSN,Amount_Due,Comments FROM INSERTED;


            OPEN cur_Client;


            FETCH NEXT FROM cur_Client INTO @SSN, @Amount_Due,@Comments;


            WHILE @@FETCH_STATUS = 0




                  Insert into dbo.Client_Base (Comments) values (@Comments)


                  set @Client_Id = scope_identity()


                  Update dbo.Client_base


                        set SSN = encryptbykey(key_guid(‘sk_encrypt_test’),@SSN,1,Convert(varchar(10),@Client_ID))


                              ,Amount_Due = encryptbykey(key_guid(‘sk_encrypt_test’),convert(varchar(10),@Amount_Due),1,Convert(varchar(10),Client_ID))


                        where Client_ID = @Client_ID




            FETCH NEXT FROM cur_Client INTO @SSN, @Amount_Due,@Comments




            CLOSE cur_Client


            DEALLOCATE cur_Client        


            CLOSE SYMMETRIC key sk_encrypt_test






  –Created Update Trigger


create TRIGGER trg_Client_Update ON dbo.Client










      open SYMMETRIC key sk_encrypt_test decryption by certificate cert_sk


      Update Client_base


            Set SSN = encryptbykey(key_guid(‘sk_encrypt_test’),i.SSN,1,Convert(varchar(10),i.Client_ID))


                  ,Amount_Due = encryptbykey(key_guid(‘sk_encrypt_test’),convert(varchar(10),i.Amount_Due),1,Convert(varchar(10),i.Client_ID))


            FROM Client_Base


            inner join inserted i on Client_Base.Client_ID = i.Client_Id




      CLOSE SYMMETRIC key sk_encrypt_test






  –Created Delete Trigger


create trigger dbo.trg_Client_Delete

      on dbo.Client








      Delete Client_Base


      from Client_Base db


      inner join deleted d on db.Client_ID = d.Client_ID








— MUST OPEN KEY or all insert statements will FAIL




DECRYPTION BY PASSWORD = N’knights12$gzmlauncher#1@%dmissionisclear*()’;




–Insert Value


Insert into dbo.client (ssn,amount_due,comments) values


      (‘123456789′,256.01,’This is a test of encryption’)




select * from dbo.client;


select * from dbo.client_base;




  –Update the value


Update Client


      set ssn = 987654321,


            Amount_Due = 100


      where ssn = 123456789;




select * from Client;




Close Master Key;