4 Effective Methods to Protect Your SQL Server Data

With digitalization all over the world, it is very important that the data saved in database is secure. Irrespective of the size, it is a fact that every database has some sensitive data and that has to be protected. This article highlights all options to secure your SQL Server data

1. Start with proper access and review it

Protect Your SQL Server DataThe first important step is to ensure that you are providing only necessary access to users. If a user wants to read some data from database, provide them with Read access. Never grant full access to all users. It is important to understand the magnitude of this step. Consider an unhappy employee with full access to the production servers; he might run a delete statement and corrupt the SQL Server database.

2. Full access to third party users:

It is very common that full access is granted to third party users for application deployment. In that case, keep track of all provided access and after the deadline revoke the access or delete the user. It is a mandatory process to conduct user audit on all servers. Review the report and revoke unwanted access or delete unwanted users. Most companies disable or remove Windows account of their ex-employees. This will ensure that Windows account on SQL database too won’t work too. However SQL authentication accounts created for ex-employees will still work and it’s a security threat until it is removed.

3. Automate disable and drop

CREATE TABLE usertable (
    servername VARCHAR(200)
    ,loginname VARCHAR(200)
    ,expirydate DATETIME
    ,solution VARCHAR(100)
    ,remarks VARCHAR(100)
    ,lastupdate DATETIME
    )

INSERT INTO usertable (
    servername
    ,loginname
    ,expirydate
    ,solution
    )
VALUES (
    'Server1'
    ,'asdf'
    ,'2016-01-19 23:04:27.440'
    ,'DROP'
    )

SELECT *
FROM usertable

DECLARE c1 CURSOR
FOR
SELECT servername
    ,loginname
    ,expirydate
    ,solution
FROM usertable

OPEN c1

DECLARE @srvr VARCHAR(200)
DECLARE @login VARCHAR(200)
DECLARE @expiry DATETIME
DECLARE @sol VARCHAR(200)
DECLARE @cmd VARCHAR(400)

FETCH NEXT
FROM c1
INTO @srvr
    ,@login
    ,@expiry
    ,@sol

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @expiry < getdate()
    BEGIN
        IF @sol = 'DROP'
        BEGIN
            SET @cmd = 'sqlcmd -S ' + @srvr + ' -d MASTER -Q "DROP LOGIN ' + @login + '"'

            PRINT @cmd

            EXEC xp_cmdshell @cmd

            UPDATE usertable
            SET remarks = 'COMPLETED'
                ,lastupdate = getdate()
            WHERE servername = @srvr
                AND loginname = @login
                AND expirydate = @expiry
                AND solution = @sol
        END
    END

    FETCH NEXT
    FROM c1
    INTO @srvr
        ,@login
        ,@expiry
        ,@sol
END

CLOSE c1

DEALLOCATE c1

4. Mask the data

SQL 2016If you have started to use SQL  2016, protecting your sensitive and confidential data is very easy with dynamic data masking. This feature helps to control the amount of data that has to be masked before being displayed on the screen. The best part is, all this protection occurs with minimal impact on the application layer. You can mask the complete data or just a part of it. All you have to do is to identify the sensitive fields in a database and create a central masking policy for those fields. Then create privileges so that users without those privileges will see the masked data whereas users with privileges will see the original data i.e., unmasked data. Please do note that the masking cannot be applied on a computed column. However if the source column of a computed column is masked, data from computed column would also be masked.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst data damage and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.