CODEWITHSUNDEEP
sql-serverencryption
HamidEbr
HamidEbr

Reputation: 405

SQL Server restrict all users to a database (using encryption)

I want to encrypt my database (in this example testDB) for all SQL Server users - even sa!

I used TDE and wrote these queries:

USE testDB
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '12345678';
CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE Certificate';

BACKUP CERTIFICATE tdeCert TO FILE = 'e:\tdecert'
    WITH PRIVATE KEY (
        FILE = 'e:\hello',
        ENCRYPTION BY PASSWORD = '12345678');

CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
        ENCRYPTION BY SERVER CERTIFICATE tdeCert;

ALTER DATABASE testDB SET ENCRYPTION ON;
GO

But simply testDB and all of its tables are shown to any users without any password! Please help to do this restriction. Thanks

Upvotes: 0

Views: 498

Answers (2)

Diado
Diado

Reputation: 2257

TDE encrypts the data 'at rest' - i.e. it encrypts the data files themselves, and decrypts them when loading them into memory using a decryption key that is stored in the database server. This is completely transparent to applications using the data (hence the name), no application changes are required to use it and no further authentication than is already implemented on the database is required to access the data.

Where TDE comes into play is if the database files themselves are somehow stolen or transferred to a different machine without permission - they cannot be restored or otherwise decrypted without the decryption key stored in the server they were encrypted on. There's more information on TDE here: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-tde

To secure the data from users by requiring a password, you need to implement SQL server logins and users, which will allow you to give certain rights to data and operations to certain users or roles. There's a walkthrough here that should get you started: https://www.concurrency.com/blog/january-2017/creating-logins-and-users-in-sql-server

Upvotes: 2

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

1.Connect to your SQL server instance using management studio
2.Goto Security -> Logins -> (RIGHT CLICK) New Login
3.fill in user details
4.Under User Mapping, select the databases you want the user to be able to access and configure

-- Or You Can Use
--Step 1: (create a new user)
create LOGIN hello WITH PASSWORD='foo', CHECK_POLICY = OFF;


-- Step 2:(deny view to any database)
USE master;
GO
DENY VIEW ANY DATABASE TO hello; 


 -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below)
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::yourDB TO hello;
GO

Upvotes: 1

Related Questions