Tag Archives: Security

New Pluralsight Course – LFCE: Network and Host Security

My new course “LFCE: Network and Host Security” in now available on Pluralsight here! If you want to learn about the course, check out the trailer here or if you want to dive right in check it out here!

This course targets IT professionals that design and maintain RHEL/CentOS based enterprises. It aligns with the Linux Foundation Certified System Administrator (LFCS) and Linux Foundation Certified Engineer (LFCE) and also Redhat’s RHCSA and RHCE certifications. The course can be used by both the IT pro learning new skills and the senior system administrator preparing for the certification exam

Let’s take your LINUX sysadmin skills to the next level and get you started on your LFCS/LFCE learning path.

If you’re in the SQL Server community and want to learn how Linux secure your Linux systems…this course is for you too! You have heard that Microsoft has SQL Server for Linux now, right, if not…read this!

The modules of the course are:

  • Linux Security Concept and Architectures – Introduction you into the fundamental concepts needed for securing your environment
  • Securing Hosts and Services – iptables and TCP Wrappers – Host based firewall concepts and techniques with iptables and TCP Wrappers
  • Securing Hosts and Services – firewalld – Learn leverage firewalld to develop more complex firewalls systems…simply. Including concepts such zones, service, ports and NAT
  • Remote Access – OpenSSH – We’ll look at encryption, authentication and how to configure SSH for public authentication
  • Remote Access – Tools and Techniques  – SSH is more than just remote access, we’ll look at secure copy, tunneling and how to use windowing systems such as X11 and VNC…securely.

Pluralsight Redhat Linux

Check out the course at Pluralsight!

Configuring Passwordless PowerShell Remoting over SSH

Open Source PowerShell has been on fire, getting tons of community support and really making people think about what’s to come with a single language to manage a heterogenous data center.

To highlight this point, in my recent Pluralsight Play By Play Microsoft Open Source PowerShell on Linux and Mac with Jason Helmick and Jeffrey Snover I did a demo on using PowerShell remoting where I connected from a Linux machine to three other machines and retrieved lists of top processes from each…two Linux and one Windows. I used one script to accomplish this and no passwords. A simple implementation highlighting a very big idea. After, some people have asked…how did I do this without passwords? 

Open Source PowerShell Remoting uses SSH as its communication protocol, so when we connect to a remote system using PowerShell Remoting we’ll need to enter a password. Normally SSH requires passwords to log into remote systems but it also allows for what’s called passwordless authentication, which means users can log into remote systems without having to key in a password. It does this, securely, by using a key pair to authenticate the user to the server. Basically you generate a key pair, copy the public key to the remote server and there you have it…you no longer have to enter a password when you SSH into the remote system. Let’s see how this is done.

You need a couple things to set up this demo

  1. A user account with the same name on each computer – create a user on each machine, Linux and Windows, with the same username.
  2. OpenSSH configured on all hosts – easy on Linux. It’s there by default. On Windows check out this link. Once you complete the installation of OpenSSH on your Windows system, test logging into that system from a remote computer with SSH. This will use the password for a user on that Windows system (likely the one you just created in step 1). If that doesn’t work, you won’t be able to proceed.
  3. Open Source PowerShell installed on all hosts – check out this link here
  4. Enable PowerShell Remoting over SSH – check out this link here. Once you have this configured, be certain to test PowerShell remoting, using passwords. Test Linux to Linux and also Linux to Windows. 

Now once we have the ability to connect to our hosts with SSH and we’ve confirmed we can use PowerShell SSH Remoting, we can move on to configuring passwordless authentication. 

First, on your Linux machine (I’m using a Mac, but there literally is no difference here) you can use your existing public key if you have one, which is stored in your home directory in .ssh/id_rsa.pub or you can generate a new one. 

To generate a new SSH key pair on your Linux machine

  1. Type sshkeygen
  2. The program will ask you for a file name, just press enter
  3. It will then ask you for a passphrase, press enter again and once more to confirm
You should get output that looks like this:

Demo-MacBook-Pro:.ssh demo$ ssh-keygen 

Generating public/private rsa key pair.

Enter file in which to save the key (/Users/demo/.ssh/id_rsa): 

Enter passphrase (empty for no passphrase): 

Enter same passphrase again: 

Your identification has been saved in /Users/demo/.ssh/id_rsa.

Your public key has been saved in /Users/demo/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:g5SyXmke+OAmYSl4nxc4wcRnsyeDO6RE9/Q9FKlcpKY demo@demo-MacBook-Pro.local

The key’s randomart image is:

+—[RSA 2048]—-+

|   ..    .oo     |

|  .oo =. .+      |

| . .+*o=o=       |

|. ..oB=== o      |

|o.=o*.E+S  .     |

| +.=oO o .       |

|  . *.+          |

|   o .           |

|                 |

+—-[SHA256]—–+

 
Copy the public key from the Linux machine to the Windows Server

Now copy the contents of the id_rsa.pub file you just created to C:\Users\username\.ssh\authorized_keys on your Windows machine. Where username is the user you want to use for Remoting. You’ll likely copy and paste the contents of this file to the remote computer…if you do this ensure the contents are all on one line. I’m not going to go into how to configure this on Linux as there are plenty of blogs about how to do this on Linux – check it out here.
 
You’ll want to make sure you copy the same public key to all the hosts you’d like to authenticate with from this private key. In our case, the two Linux machines and the Windows machine have the same public key in the authorized_keys file on each server, inside user accounts with the same name.
 
Confirm you have Authorized Keys configured on your Windows SSH server
 
Now on the Windows machine in C:\Program Files\OpenSSH\sshd_config verify that this line is uncommented, which it should be by default. If not, uncomment it and restart the ssh service. This is the place SSH will look for keys when a user logs into the system via…SSH.

AuthorizedKeysFile .ssh/authorized_keys

Make sure that if you’re running SSH as a service, the account the service is running as had the ability to read this file. In my case the account NT SERVICE\SSHD needed read access.

Confirm SSH passwordless access from Linux (or Mac) to Windows

With that you should be able to connect from your Linux (or Mac) to your Windows machine from the machine where you generated your SSH key without any password. Likewise for your Linux machines.

Demo-MacBook-Pro:~ demo$ ssh demo@172.16.94.9

Microsoft Windows [Version 10.0.14393]

(c) 2016 Microsoft Corporation. All rights reserved.

 

demo@DESKTOP C:\Users\demo>

Let that sink in for a second, I just SSH’d into a Windows machine…

…and finally connect via PowerShell remoting over SSH with passwordless authentication

OK now we’re in the home stretch…we can now create a PowerShell remoting session over SSH with passwordless authentication. 

PS /Users/demo> Enter-PSSession-HostName 172.16.94.9 -UserName demo                                       

[172.16.94.9]: PS C:\Users\demo\Documents>

And there we have it we’re able to connect to using PowerShell Remoting over SSH without a password.

Questions about Linux? PowerShell? Please feel free to ask aen@centinosystems.com or on Twitter @nocentino

 

Encrypting Connections To SQL Server Using Certificates

Encrypting Connections To SQL Server Using Certificates

In this post we’re going to cover configuring a connection string in .NET applications for encrypting connections to SQL Server using certificates. The audience for this document is a developer that needs to configure encrypted connections from applications to a database server.

Encrypting connections with SQL Server using Certificates consists of two parts:

  • An appropriately configured connection string
  • A server certificate installed on the Database Engine (not covered in this post)

Configuring a Connection String

To configure a .NET connection string you will need to set the following parameters

  • Server – the fully qualified domain name (FQDN) of the SQL Server. The name here will need to exactly match the server common name or a subject alternative name configured in the certificate.
  • Database – the database context used for this connection
  • uid – the username of this connection
  • password – the password for the user
  • Encrypt – set to ‘yes’ to encrypt or ‘no’ or remove to disable encryption 
Optional
  • TrustServerCertificate – will bypass validating the certificate. This is useful if the certificate is untrusted, the common name or subject alternative name do not match what is in the certificate or the certificate is expired. This is useful for testing and troubleshooting. Enabling this in production environments is strongly discouraged as the destination certificate is not validated.

Configuring a Connection String On a Single Instance

When connecting to a single instance, a valid certificate must be installed on the instance. The server parameter in the connection string needs to be configured as the subject/common name in the certificate and the server configured in the connection string must match this name. In figure 1, the subject is sql14-a.lab.centinosystem.com the connection string server parameter should match this name.

Certificate

Figure 1 – Certificate with Subject/Common Name

Configuring a Connection String On Availability Groups

A valid certificate must be installed on all servers/replicas in the Availability Group. The subject/common name in the certificate should match the local server. The subject alternative names in the certificate should match the availability group listener DNS name. The server parameter of the connection string will use this name. In figure 2, the AG listener is ag1lst.lab.centinosystems.com, the connection string server parameter should match this name.

Subject Alternative Name

Figure 2 – Certificate with Subject Alternative Names

Configuring a Connection String With Aliases

Often application connection strings are configured using an alias (DNS CNAME), rather than the actual host name (A record) of the SQL Server. Upon the connection’s DNS request, the DNS server responds to the CNAME request for the alias with the A record data for the actual server.  This means the certificates common name or subject alternative name do not have to include the alias (DNS CNAME). This applies to single instances and Availability Groups.

Example Connection Strings

    1. Example connection to a single instance when the server has a valid certificate

      <connectionStrings><add name=myConnectionString connectionString=server=sql14-a.lab.centinosystems.com;database=TestDB1;uid=user1;password=s3cur31y;Encrypt=yes/></connectionStrings>

    2. Example connection to an availability group listener when each server has a valid certificate with the appropriate subject alternative name

      <connectionStrings><add name=myConnectionStringconnectionString=server=ag1lst.lab.centinosystems.com;database=TestDB1;uid=user1;password=s3cur31y;Encrypt=yes/></connectionStrings>

Errors

Here are a few errors that you may encounter when connecting to SQL Server with an encrypted connection. Basically when validation fails there will be an exception thrown with information similar to these examples.

When opening a connection string asking for encryption, but an untrusted certificate

System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.) —> System.ComponentModel.Win32Exception (0x80004005): The certificate chain was issued by an authority that is not trusted

When opening a connection string, but the certificate is expired

System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The received certificate has expired.) —> System.ComponentModel.Win32Exception (0x80004005): The received certificate has expired

References:

Connection String Syntax – https://msdn.microsoft.com/en-us/library/ms254500(v=vs.110).aspx

Enable Encrypted Connections to the Database Engine – https://msdn.microsoft.com/en-us/library/ms191192(v=sql.110).aspx