Feeds:
Posts
Comments

Archive for the ‘Security’ Category

Using SSH keys provide a more secure way of logging into a remote computer when compared to password authentication, and today I will walk you through how we can achieve this in 3 simple steps

For this demo I will be configuring SSH key authentication for the user account accountsguru to connect to the remote system mylinuxlab.net, accessing remotely from my local computer sraavi.

  • user account: accountsguru
  • local computer: sraavi
  • remote system: mylinuxlab.net

Prerequisite: User accountsguru must be having an account already existing in the remote system mylinuxlab.net and authorized to access remotely.

Step1: Generate SSH public-private key pair

Logon to the local computer with the user account for which we want to create the SSH key pair, and run the following command

ssh-keygen

Below is the output generated. If you watch closely, in line 3 we are prompted to chose a directory and I accepted the default here, and in the next line we are prompted to enter a passphrase, which is to protect your private key. Passphrase adds an additional security layer because if in case a hacker got access to your private key he/she won’t be able to make any use as the private key is passphrase protected. Since we are doing a demo here I skipped the passphrase

[accountsguru@sraavi ~]$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/accountsguru/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/accountsguru/.ssh/id_rsa.
Your public key has been saved in /home/accountsguru/.ssh/id_rsa.pub.
The key fingerprint is:
7b:54:3e:f8:33:31:8e:70:81:f1:a3:4d:e2:52:c3:0b accountsguru@sraavi
The key's randomart image is:
+--[ RSA 2048]----+
| . |
| . + |
| E * = . |
| + B * |
| . S = = |
| . = + + |
| . o = |
| . o |
| |
+-----------------+

From the output above, line 6 is our private key, and line 7 is the public key.

Step2: Copy the public key to the remote system

Now, copy the public key from your local computer to the remote system using the below command

ssh-copy-id accountsguru@mylinuxlab.net

Note that it will prompt to enter the password to access the remote computer, and here is how the result looks like

[accountsguru@sraavi ~]$ ssh-copy-id accountsguru@mylinuxlab.net
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
accountsguru@mylinuxlab.net's password:
Number of key(s) added: 1

From the above two steps we’ve successfully generated key pair and configured the user account accountsguru to access remotely using SSH

Step3: Connect to the remote system using SSH

Now let’s try logging into the remote server using SSH with the following command

ssh accountsguru@mulinuxlab.net

And, here is how it looks after making a successful connection..

[accountsguru@sraavi ~]$ ssh accountsguru@mylinuxlab.net
Last login: Fri Dec 9 19:28:33 2016 from 172.110.22.205
[accountsguru@mylinuxlab ~]$

To exit the remove server you can press tilda followed by dot (~.) and usually we won’t see the characters when we type them, but the session will terminate immediately

[accountsguru@mylinuxlab ~]$ Connection to mylinuxlab.net closed.

Hope this helps! If you have any feedback or a question, please leave it in the comment section below.

Advertisements

Read Full Post »

In this post, let’s learn how to use chgrp and chown commands to change group and user ownership of a directory

On a Linux server, by default, the group owner of a file or directory is the primary group of the user who created the file directory. And it is highly likely in most cases the primary group and the user share the same name

Let’s say we need to change the group and user ownership of the directory /home/chris/mars to root user, below are the steps we need to execute

Step1: Switch to root user

#switch to the root user
su - root

Note: In order to change the group owner of a file or directory, one must be the user owner of the file AND be a member of the group to which we are changing ownership or else be the root user. Also, remember that only the root user can change the user ownership of a file or directory.

Step2: Use chgrp to change the group owner and chown to change the user owner

#Using chgrp to change the group owner
chgrp root /home/chris/mars
#Using chown to change the user owner
chown root /home/chris/mars

OR
Step3: Use chown to change both group owner and user owner at the same time

#using chown to change both group and user owner at the same time
chown root:root /home/chris/mars

Here’s a bonus tip for you: The process to change group and user ownership on a file is the same as performing the commands on a directory, making our job easy!

Read Full Post »

Most people prefer to have “sa”  account as the database owner, primary reason being sa login cannot be removed/deleted unlike any user account or service account and so the databases will never end-up in an orphaned stage.

I came-up with the below method to change the ownership to sa on all the 40 databases in our environment recently.

Step 1: Check the databases that does not have sa account as the owner

SELECT name AS DBName, suser_sname(owner_sid) AS DBOwner  
FROM sys.databases
WHERE suser_sname(owner_sid) <> 'sa'
 

Step 2: Generate the scripts to make sa account as owner for all the databases

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];'
from sys.databases
where name not in ('master', 'model', 'tempdb', 'msdb')
AND suser_sname(owner_sid) <> 'sa'
 

Step 3: Execute the result set from step 2 above to change the ownership to sa

--Sample result set from step2 above
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT2012] TO [sa];
ALTER AUTHORIZATION ON DATABASE::[Northwind] TO [sa];
ALTER AUTHORIZATION ON DATABASE::[Pubs] TO [sa];
 

For more information on sa account you can check my previous blog post HERE

Read Full Post »

Most of us know the default port for SQL Server is 1433, but there are various ports being used by SQL Server for other database services and SQL features.

In the below table I tried to list the ports that are frequently used by the Database Engine

Item Port
Default instance TCP port 1433
Named instance in default configuration Dynamic port. You can configure named instances to use fixed TCP ports
Dedicated Admin Connection (DAC) TCP port 1434
SQL Server Browser service UDP port 1434
SQL Server instance running over an  HTTP end-point TCP port 80 for CLEAR_PORT traffic   TCP port 443 for SSL_PORT traffic
Service Broker TCP port 4022
Replication TCP port 1433 for default instance
Transact-SQL Debugger TCP port 135
Analysis Services TCP port 2383 for default instance
Reporting Services Web Services TCP port 80
Reporting Services configured for use through HTTPS TCP port 443
Integration Services: Microsoft remote
procedure calls
TCP port 135
Integration services run time TCP port 135
Microsoft Distributed Transaction Coordinator (MS DTC) TCP port 135
SQL Server Management Studio browse connection to browser service UDP port 1434

You can find more information about the TCP and UDP ports SQL Server uses from books online here: Configure the Windows Firewall to Allow SQL Server Access

Read Full Post »

In this article I will show you how to restore a database that is encrypted with Transparent Data Encryption (TDE) on to a different server. Let’s use the same database ‘TDE_Test’ that we have created in the previous post HERE

Restoring a database to a different SQL Instance is usually a straightforward task. However, this attempt will return an error as shown below for an encrypted database when restoring into a different instance.

USE [master]
RESTORE DATABASE [TDE_Test_restore] FROM  
DISK = N'C:\Backup\TDE_Test_withtde.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Output:

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint..
Msg 3013, Level 16, State 3, Line 2
RESTORE DATABASE is terminating abnormally

To restore successfully, we will need to physically copy the certificate (.cer) and private key (.pvk) to the destination server. As a best practice, we should immediately back up the certificate and the private key when we enable TDE. However, we can still take backup the certificate and private key now in the source server as shown below if not done earlier.

USE master; 
GO 
BACKUP CERTIFICATE TDECert
TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Password12#')

Create a Master Key in destination server.

The password provided here is different from the one we used in the source server since we are creating a new master key for this server.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D1ffPa$$w0rd'

After a master key has been created, create a certificate by importing the certificate we created earlier. Here the ‘Decryption By Password’ parameter is same as that provided to export the certificate to a file.

CREATE CERTIFICATE TDECert2
FROM FILE = 'E:\cert_Backups\ certificate_TDE_Test_Certificate.cer'     
WITH PRIVATE KEY (FILE = 'E:\cert_Backups\certificate_TDE_Test_Key.pvk', 
DECRYPTION BY PASSWORD = 'Password12#')

Restore Database in destination server

We will now be able to restore the encrypted database backup successfully.

USE [master]
RESTORE DATABASE [TDE_Test] FROM  DISK = N'F:\Backup\TDE_Test_withtde.bak' 
WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5

Listed are some of the advantages and disadvantages of using TDE.

Advantages

  • The performance impact of TDE is minor. Estimated to be around 3-5%
  • Performs real-time I/O encryption and decryption of the data and log files
  • Encrypts the Entire Database in rest
  • No architectural changes needed
  • No application code changes are required and the user experience is the same
  • Easy to implement
  • DBAs can still see the data

Disadvantages

  • No protection for data in memory
  • Not granular – Cannot just encrypt specific tables/columns
  • Not good for high CPU bottleneck servers
  • Not protected through communication/networks

Read Full Post »

Being a part of the database engineering team, I was given a task to setup disaster recovery program for some of our SQL Servers hosting critical applications. After a few discussions, I planned to make use of log shipping to sync data between the two data centers like most DR sites work. This requires us to copy the backup files to the Disaster Recovery (DR) site for the initial setup. But in a little while, I realized moving 30 TB of data over network is a Herculean task considering our extra large databases at an approximate 12-15 mbps over network. This might not be a feasible option with 45 days of wait time along with something that chews up all our bandwidth. So we rather decided to ship the hard drives in a truck to the DR site which is just a few hundreds miles away.

Everything was good until one of the IT Directors who overheard this while he was passing by DBAs  was curious and asked if it isn’t a security risk if we lose our disks during the transit?

I jumped in and replied him with a smile, “Not when we encrypt our data with Transparent Data Encryption”

Lets see what Transparent Data Encryption is?

Transparent Data Encryption is a new feature in SQL Server 2008 which allows the encryption of the entire database while providing real time encryption of data files (.mdf) and log files (.ldf). Apart from this, TDE also encrypts any backups taken while the encryption is enabled. Data is encrypted before it is written to disk and data is decrypted when it is read from disk as it is read into the memory. This will help in preventing unauthorized access to the data and backup files.

Demo

I will show you an example of how anyone can read the backup files even without restoring the backup into another database.

I have created a database called TDE_Test that contains address details as shown below.(This database basically contains a few tables imported from the AdventureWorks2012 sample database. For downloading and installing AdventureWorks2012, refer HERE)

1

Figure 1: Result set showing Address data

Let’s backup the ‘TDE_Test’ database

BACKUP DATABASE [TDE_Test] 
TO DISK = 'E:\Backup\TDE_Test__Backup.bak' 
WITH INIT, STATS = 5

Open the backup file using Notepad and search for address ‘‘5 4 4   M a g d a   W a y’, as shown in Figure 2 (note the spaces between the letters and three spaces between each word as this is Unicode text)

We can easily see that the data in backup file is readable. This means anyone with the backup file can actually read your data with little skill, even without restoring it. 

2

Figure 2: Backup file opened in Notepad – Un-encrypted format

Implementing Transparent Data Encryption

TDE setup includes the below four basic steps.

  1. Creating a Master Key
  2. Creating a Certificate Protected by the Master Key
  3. Creating a Database Encryption Key
  4. Turning on the TDE

1. Creating a master key 

It is a good practice to backup the database before proceeding with the encryption. So in case we want to reverse the implementation of TDE we have a latest copy of database to bring back.

The master key must be created within the master database

USE MASTER; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password12#'
GO

2. Creating a Certificate Protected by the Master Key

The certificate is used to protect the database encryption key that we will create next. In addition, this certificate is protected by the master key created in the previous step.

USE MASTER; 
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate'
GO

3. Creating a Database Encryption Key

Create Database Encryption Key inside the database to be encrypted, and protect it with the certificate. You can choose from one of the following encryption algorithms: AES_128, AES_192, AES_256, or TRIPLE_DES_3KEY. Details on the encryption algorithms is beyond the scope of this article. Check out this Microsoft Technet article for guidance on choosing the encryption algorithm

USE TDE_Test  

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

I have also added an additional script below to backup the private key and certificate to disk drive. Since TDE also encrypts the backup files, it is always required to maintain a backup of private key and certificate along with the database backup to use at the time of restoring.

USE master; 
GO 
BACKUP CERTIFICATE TDECert 
TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Password12#')

4. Turning TDE ON

Since encryption is a resource intensive process it is always a best practice to turn on TDE during off-business hours or when the server is least busy.

USE  TDE_Test
ALTER DATABASE  TDE_Test
SET ENCRYPTION ON ; 
GO

We can check the progress of Encryption using the below script from utilizing ‘sys.dm_database_encryption_keys’ DMV. A value of ‘2’ for ‘encryption_state’ indicates that encryption has begun and ‘3’ for completed

SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm
FROM sys.databases db JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id WHERE db.name = 'TDE_TEST'

Let’s take a backup of our encrypted database now and search for address as mentioned earlier.

BACKUP DATABASE [TDE_Test] 
TO DISK = 'E:\Backup\TDE_Test_withtde.bak' 
WITH INIT, STATS= 5

4
Figure 3: Backup file opened in Notepad – Encrypted format

Whoopee! Our data is now encrypted and secured 🙂

This way, we can make sure the data is secured as well protected from any unauthorized access.

As a continuation to this topic,  I have explained how to restore a TDE Encrypted database backup HERE

Technical Reviewer(s): Venkata Suresh Raavi; Jaipal Vajrala

Read Full Post »

Ever noticed and wondered why the well-known SQL Server system administrator (sa) login is in a disabled state?

SA Login

The reason is simple, sa login account is disabled out of the box (by default) in Windows Authentication mode. You have to enable manually to use it.

On the other hand, if you request Mixed Mode Authentication during installation, SQL Server Setup prompts you to set an sa login password. So sa login account is enabled in this case.

Enable/Disable sa login using SSMS GUI:

  • From the Object Explorer, expand “Security” and then expand “Logins” –> Right click on sa and select “Properties”

SA Login - 1

  • On the “General” tab, create a strong password

SA Login - 2

  • On the “Status” tab, click “Enabled”, and then click “ok” (If sa is already enabled, you have to chose “Disabled” to disable the same)

SA Login - 3

Enable sa login using T-SQL script:

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '$trongPa$$w@rD';
GO

Disable sa login using T-SQL script:

ALTER LOGIN sa DISABLE ;
GO

Here’s some more info about “sa” account:

  • System administrator (sa) is a special login provided for backward compatibility
  • Usually there is no effect sa being in a disabled state though it pertains and owns the system databases
  • By default, sa login is assigned to the sysadmin fixed server role and cannot be changed
  • Microsoft do not recemmond using sa login in application (as it is often targeted by malicious users)
  • However, Microsoft recommonds using sa only when there is no other way to log in to an instance
  • The sa login cannot be removed/deleted

Technical Reviewer: Jaipal Vajrala

Read Full Post »

Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing.

Doing it from SSMS GUI will take forever. So, I wrote a simple T-SQL script using which I was able to pull out all that information in less than a second!

Get the list of all Login Accounts in a SQL Server

SELECT name AS Login_Name, type_desc AS Account_Type
FROM sys.server_principals 
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc

Get the list of all SQL Login Accounts only

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'S'
and name not like '%##%'

Get the list of all Windows Login Accounts only

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'U'

Get the list of all Windows Group Login Accounts only

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'G'

Note: Requires ALTER ANY LOGIN server permission to be able to view all the logins.

Read Full Post »

As of today (5/14/2013) this MSDN article says “SQL Server Agent service startup account must be a member of the SQL Server sysadmin fixed server role”

However, in one of our environments (SQL 2012) I have seen, just a public fixed server role is good enough to start the SQL Agent services and it works very well.

So is the statement mentioned in the MSDN article incorrect? looks like it is..moreover later I figured out the account (either a domain account or built-in system account) used for running any of the SQL Server services may not necessarily have a SQL login at all…sounds interesting?

Before going further on this topic let’s first understand What is a Per-Service SID?

SQL 2008 introduced this new option of SID (Security Identifier), a mechanism that assigns privileges to the SQL service itself, rather than to the account under which the service runs.

So starting SQL Server 2008+ on Windows Server 2008+ here is how Service Configuration and Access Control is managed:

  • SQL Server enables per-service SID for each of its services to provide service isolation and defense in depth
  • This per-service SID is derived from the service name (e.g. Database Engine/SQL Server Agent/SSIS/SSRS..etc) and is unique to that service
  • For example, a service SID name for the Database Engine service might be NT Service\MSSQL$<InstanceName>

Service SIDs managed to improve our security because:

  • Service isolation enables access to specific objects without the need to run a high-privilege account or weaken the security protection of the object
  • By using an access control entry that contains a service SID, a SQL Server service can restrict access to its resources

And here’s my understanding after some research

  • The account (either a domain account or built-in system account) used for running SQL Server services may not necessarily have a SQL login if the Per Service SID has a login instead
  • SQL Server DB Engine and SQL Server Agent runs as Windows services named NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT respectively within the Windows environment.
  • And the Service Account which we specify to run the services will be used when a process tries to reach outside of the current Windows environment.
  • Hence, the Service Startup Account will be used for off-box operations, unless it’s a built-in account like Network Service or a Virtual Account and if the server is Domain-joined, in which case the Machine Account will be used.
  • On SQL 2012 separate Service Startup account can be eliminated and the SQL Services can run as the “Virtual Accounts”, which have the same name as the Per Service SID, but which also have a system-managed password.

Always remember SQL Server Secuirty is all about Principle of Least Privilege. For more specific information refer MSDN BOL: Configure Windows Service Accounts and Permissions

Read Full Post »