Table of Contents

Introduction





Usually opening a connection to the Microsoft SQL-Server database involves no security configuration for small personal applications, since we use the authentication of the person who execute the application by default, but professional workplace applications utilize Windows authentication using specific user for the Windows Form application. This article will provide code for developers who want their users to perform a login by providing a user name and associated password each time there is a need to access data within a database. 


VB.NET Version

Prerequisites

Before writing any code users must be created for the intended database along with permissions to each table the user will need access too.

Server login/permission basics resources

TechNet: SQL Server Logins: Back To Basics - learn about creating logins

TechNet: SQL Server: Database Engine Permission Basics 

Microsoft: Create user documentation

Microsoft: Grant database permission

Creating users

Creating users can be done in SSMS or in script were the script may be in SSMS or in a new query window created in Visual Studio's Server Explorer Window.

Simple example:

CREATE LOGIN KarenPayne WITH PASSWORD = 'password1',DEFAULT_DATABASE = [UserLoginExample]
CREATE USER KarenPayne FOR LOGIN KarenPayne;

Login failure error code 18456

This error may happen when logging in from code were in the Exception the State = 1. There are several reasons for this to happen while a common reason is the SQL Service MSSQL$SQLEXPRESS (For Express edition) authentication mode is Windows Authentication and needs to be set to mix mode, SQL Server and Windows Authentication mode. In SSMS Connection to the server, with Object Explorer open, right click the top node, select Security node and set as shown below.

 


Once this is done, open Task Manager to services and restart the service.

Validating users

Best way to validate users is using SSMS (SQL-Server Management Studio) by traversing to the security tab under the database using the Object Explorer.

Checking permissions can be done in SSMS by creating a new query window and point to the catalog using the following query where @LoginName is the user to check table permissions on. For more on this query see the following TechNet article.

DECLARE @LoginName AS NVARCHAR(50) = 'karenpayne'
SELECT perms.state_desc AS State ,
       permission_name AS [Permission] ,
       obj.name AS [on Object] ,
       dPrinc.name AS [to User Name] ,
       sPrinc.name AS [who is Login Name]
FROM   sys.database_permissions AS perms
       JOIN sys.database_principals AS dPrinc ON perms.grantee_principal_id = dPrinc.principal_id
       JOIN sys.objects AS obj ON perms.major_id = obj.object_id
       LEFT OUTER JOIN sys.server_principals AS sPrinc ON dPrinc.sid = sPrinc.sid
WHERE  sPrinc.name = @LoginName;

 Managing sensitive login details

Since user name and user password will be travelling between forms and classes this information must be protected. The following class may not meet each developers security requirements, if not replace with what meets these requirements.

using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;
 
namespace LoginLibrary.SecurityClasses
{
    namespace SecurityClasses
    {
        /// <summary>
        /// Simple encryption decryption of strings
        /// </summary>
        public class Encryption
        {
            public byte[] Encrypt(string plainText, string secretKey)
            {
                byte[] encryptedPassword = null;
                using (var outputStream = new MemoryStream())
                {
                    RijndaelManaged algorithm = getAlgorithm(secretKey);
                    using (var cryptoStream = new CryptoStream(outputStream,
                        algorithm.CreateEncryptor(), CryptoStreamMode.Write))
                    {
                        byte[] inputBuffer = Encoding.Unicode.GetBytes(plainText);
                        cryptoStream.Write(inputBuffer, 0, inputBuffer.Length);
                        cryptoStream.FlushFinalBlock();
                        encryptedPassword = outputStream.ToArray();
                    }
                }
                return encryptedPassword;
            }
 
            public string Decrypt(byte[] encryptedBytes, string secretKey)
            {
                string plainText = null;
                using (var inputStream = new MemoryStream(encryptedBytes))
                {
                    RijndaelManaged algorithm = getAlgorithm(secretKey);
                    using (var cryptoStream = new CryptoStream(inputStream,
                        algorithm.CreateDecryptor(), CryptoStreamMode.Read))
                    {
                        byte[] outputBuffer = new byte[((int)(inputStream.Length - 1)) + 1];
                        int readBytes = cryptoStream.Read(outputBuffer, 0, (int)inputStream.Length);
                        plainText = Encoding.Unicode.GetString(outputBuffer, 0, readBytes);
                    }
                }
                return plainText;
            }
            private RijndaelManaged getAlgorithm(string secretKey)
            {
                const string salt = "akl~jdf";
                const int keySize = 256;
 
                var keyBuilder = new Rfc2898DeriveBytes(secretKey, Encoding.Unicode.GetBytes(salt));
                var algorithm = new RijndaelManaged {KeySize = keySize};
                algorithm.IV = keyBuilder.GetBytes(Convert.ToInt32(algorithm.BlockSize / 8.0));
                algorithm.Key = keyBuilder.GetBytes(Convert.ToInt32(algorithm.KeySize / 8.0));
                algorithm.Padding = PaddingMode.PKCS7;
                return algorithm;
            }
        }
    }
}

Login window

This window/form consist of a TextBox for user name, a TextBox for user password were the password is masked with the option to unmask using a CheckBox to toggle visibility. A Button to perform the login, another button to cancel logging in.

There are no limitations for failed login attempts. 

Once clicking the OK (login button) button an assertion is performed to ensure both user name and user passwords have been entered. If either is missing no login attempt is executed. If both user name and user password have been entered an instance of the Encryption class is instantiated followed by encrypting both user name and user password.

var encryption = new Encryption();
// encrypt user name and password
var userNameBytes = encryption.Encrypt(UserNameTextBox.Text, "111");
var passwordBytes = encryption.Encrypt(PasswordTextBox.Text, "111");

This is followed by creating an instance of the DatabaseUser class which attempts to connection to a specific database on a specific instance of SQL-Server where the encrypted user name and user password are used in a connection string. Both server name and default catalog are passed through the constructor for DatabaseUser class.

An attempt is made to connect within a try/catch where there are multiple catch sections, one for failed login in regards to user name and user password, another for any other sql server exception and one for general exceptions.

Returning results is the responsibility of the following class.

namespace LoginLibrary.SupportClasses
{
    namespace SupportClasses
    {
        public class SqlServerLoginResult
        {
            public bool Success {get; set;}
            public bool Failed => Success == false;
            public bool GenericException {get; set;}
            public string Message {get; set;}
 
            public override string ToString()
            {
                return Message;
            }
        }
    }
}
DatabaseUser class which determine if a connection can be established, if so or not the connection objects are disposed of before returning to the login form.
using System;
using System.Data.SqlClient;
using LoginLibrary.SecurityClasses.SecurityClasses;
using LoginLibrary.SupportClasses.SupportClasses;
 
namespace LoginLibrary.DataClasses
{
    namespace DataClasses
    {
        /// <summary>
        /// Responsible to validating a user has permissions
        /// to access the database, not tables.
        /// </summary>
        public class DatabaseUser
        {
            private string serverName;
            private string catalogName;
            public DatabaseUser(string pServerName, string pCatalogName)
            {
                serverName = pServerName;
                catalogName = pCatalogName;
            }
            public SqlServerLoginResult Login(byte[] pNameBytes, byte[] pPasswordBytes)
            {
                var loginResult = new SqlServerLoginResult();
 
                var secureOperations = new Encryption();
                var userName = secureOperations.Decrypt(pNameBytes, "111");
                var userPassword = secureOperations.Decrypt(pPasswordBytes, "111");
 
 
                string ConnectionString =
                    $"Data Source={serverName};" +
                    $"Initial Catalog={catalogName};" +
                    $"User Id={userName};Password={userPassword};" +
                    "Integrated Security=False";
 
                using (var cn = new SqlConnection {ConnectionString = ConnectionString})
                {
                    try
                    {
                        cn.Open();
                        loginResult.Success = true;
                    }
                    catch (SqlException failedLoginException) when (failedLoginException.Number == 18456)
                    {
                        loginResult.Success = false;
                        loginResult.GenericException = false;
                        loginResult.Message = "Can not access data.";
                    }
                    catch (SqlException genericSqlException)
                    {
                        loginResult.Success = false;
                        loginResult.GenericException = false;
                        loginResult.Message = "Can not access data.";
                    }
                    catch (Exception ex)
                    {
                        loginResult.Success = false;
                        loginResult.GenericException = true;
                        loginResult.Message = ex.Message;
                    }
                }
 
                return loginResult;
 
            }
        }
    }
}

Back in the login form results are inspected, if the connection was successful the login form is hidden, an instance of the application main form is created passing in the encrypted user name and user password (will be decrypted later).
var results = ops.Login(userNameBytes, passwordBytes);
 
//
// Login recognized (does not know if the user has proper permissions to the tables at this point)
//
if (results.Success)
{
    Hide();
    var mainForm = new MainForm(userNameBytes, passwordBytes);
    mainForm.ShowDialog();
}
else
{
    MessageBox.Show(results.Message);
}

In the main form constructor encrypted user name and user password are assigned to private form level variables which will be used in the main form load event (this may be done in form shown event rather than the main form load event if so desired).
using System;
using System.Windows.Forms;
using LoginLibrary.DataClasses.DataClasses;
 
namespace LoginUserInterface
{
    public partial class MainForm
    {
 
        private readonly byte[] _userNameBytes;
        private readonly byte[] _userPasswordBytes;
 
        private readonly BindingSource _productBindingSource = new BindingSource();
 
        public MainForm(byte[] pNameBytes, byte[] pPasswordBytes)
        {
 
            InitializeComponent();
 
            _userNameBytes = pNameBytes;
            _userPasswordBytes = pPasswordBytes;
 
        }
        private void MainForm_Load(object sender, EventArgs e)
        {
 
            var ops = new DataOperations(
                _userNameBytes,
                _userPasswordBytes,
                "KARENS-PC",
                "UserLoginExample");
 
            var productTable = ops.ReadProductsByCategory(1);
            if (ops.IsSuccessFul)
            {
                _productBindingSource.DataSource = productTable;
                ProductsDataGridView.DataSource = _productBindingSource;
            }
            else
            {
                MessageBox.Show($"Encountered issues: {ops.LastExceptionMessage}");
            }
 
        }
        private void MainFormClosed(object sender, FormClosedEventArgs e)
        {
            Application.ExitThread();
        }
    }
}

Above, in form load an instance for the data class responsible for reading data is created passing in user name, user password, server name and database name.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using LoginLibrary.SecurityClasses.SecurityClasses;
using SupportLibrary;
 
namespace LoginLibrary.DataClasses
{
    namespace DataClasses
    {
 
        public class DataOperations : BaseExceptionProperties
        {
            private string ConnectionString;
             
            public DataOperations(byte[] pNameBytes, byte[] pPasswordBytes, string pServerName, string pCatalogName)
            {
 
                var secureOperations = new Encryption();
 
                ConnectionString =
                    $"Data Source={pServerName};Initial Catalog={pCatalogName};" +
                    $"User Id={secureOperations.Decrypt(pNameBytes, "111")};" +
                    $"Password={secureOperations.Decrypt(pPasswordBytes, "111")};" +
                    "Integrated Security=False";
 
                Console.WriteLine();
            }
            /// <summary>
            /// Connect to database via validated user name and password passed in the
            /// new constructor.
            ///
            /// There are still failure points which include permissions to the tables
            /// for the user.
            /// </summary>
            /// <param name="pCategoryIdentifier"></param>
            /// <returns></returns>
            public DataTable ReadProductsByCategory(int pCategoryIdentifier)
            {
 
                mHasException = false;
 
                var productDataTable = new DataTable();
                var selectStatement =
                    "SELECT " +
                    "P.ProductID , P.ProductName , P.SupplierID , P.CategoryID , " +
                    "P.QuantityPerUnit , P.UnitPrice , P.UnitsInStock , " +
                    "S.CompanyName AS Supplier FROM dbo.Products AS P " +
                    "INNER JOIN dbo.Categories AS C ON P.CategoryID = C.CategoryID " +
                    "INNER JOIN dbo.Suppliers AS S ON P.SupplierID = S.SupplierID " +
                    "WHERE(P.CategoryID = @CategoryID); ";
 
                using (var cn = new SqlConnection {ConnectionString = ConnectionString})
                {
                    using (var cmd = new SqlCommand {Connection = cn})
                    {
 
                        cmd.Parameters.AddWithValue("@CategoryID", pCategoryIdentifier);
                        cmd.CommandText = selectStatement;
 
                        try
                        {
                            cn.Open();
                            productDataTable.Load(cmd.ExecuteReader());
 
                            var identifiers = productDataTable
                                .Columns.Cast<DataColumn>()
                                .Where((column) => column.ColumnName.EndsWith("ID"))
                                .Select((item) => item.ColumnName).ToList();
 
                            foreach (string columnName in identifiers)
                            {
                                productDataTable.Columns[columnName].ColumnMapping = MappingType.Hidden;
                            }
 
                        }
                        catch (Exception ex)
                        {
                            mHasException = true;
                            mLastException = ex;
                        }
                    }
                }
 
                return productDataTable;
 
            }
        }
    }
}


All code presented there is both assertion and proper exception handling which is important when writing code, especially when working with external databases.

Extending login to changing user's passwords

The SqlClient data provider provides a method to change passwords for users which by using a connection using encryption methods used in this article passwords can be changed using SqlConnection.ChangePassword Method which has two overloads.

class Program
{
    static void Main(string[] args)
    {
        SqlConnection.ChangePassword(
            "Data Source=a_server;Initial Catalog=a_database;UID=user;PWD=old_password",
            "new_password");
    }
}

Using in your project

  • Add the class project LoginLibrary and class project SupportLibrary to your solution (both must be referenced in your form project).
  • Follow code in the LoginUserInterface project to implement.

Alternate Login Framework 4.5 or higher

SqlCredential class Jump provides a more secure way to specify the password for a login attempt using SQL Server Authentication.  Here the credentials are set outside of the connection string to mitigate the memory dump vulnerability for keeping username and password in the connection string. This helps mitigate the threat of credentials being leaked out to the page file in a page swap or being evident in a crash dump.

To use credentials working with the code sample above the method signature remain the same as well as the try-catch. The difference between this method and the latter is SqlCredential requires a SecureString along with setting Credential property of the Connection to the new instance of SqlCredential.

public SqlServerLoginResult SqlCredentialLogin(byte[] pNameBytes, byte[] pPasswordBytes)
{
    var loginResult = new SqlServerLoginResult();
    var secureOperations = new Encryption();
 
    var userName = secureOperations.Decrypt(pNameBytes, "111");
    var userPassword = secureOperations.Decrypt(pPasswordBytes, "111");
 
    string connectionString = $"Data Source={serverName};" +
                              $"Initial Catalog={catalogName};";
 
 
    var securePassword = new SecureString();
 
    foreach (var character in userPassword)
    {
        securePassword.AppendChar(character);
    }
 
    securePassword.MakeReadOnly();
 
    var credentials = new SqlCredential(userName, securePassword);
 
    using (var cn = new SqlConnection {ConnectionString = connectionString})
    {
        try
        {
            cn.Credential = credentials;
            cn.Open();
            loginResult.Success = true;
        }
 
        catch (SqlException failedLoginException) when (failedLoginException.Number == 18456)
        {
            loginResult.Success = false;
            loginResult.GenericException = false;
            loginResult.Message = "Can not access data.";
        }
        catch (SqlException genericSqlException)
        {
            loginResult.Success = false;
            loginResult.GenericException = false;
            loginResult.Message = "Can not access data.";
        }
        catch (Exception ex)
        {
            loginResult.Success = false;
            loginResult.GenericException = true;
            loginResult.Message = ex.Message;
        }
 
    }
 
    return loginResult;
 
}

The login form code is the exact same as the first code sample except for calling SqlCredentialLogin rather than Login.

using System;
using System.Windows.Forms;
using LoginLibrary.DataClasses.DataClasses;
using LoginLibrary.SecurityClasses.SecurityClasses;
 
namespace SqlCredentialLoginInterface
{
    public partial class LoginForm
    {
        public LoginForm()
        {
            InitializeComponent();
        }
        private void ShowHidePasswordCheckBox_CheckedChanged(object sender, EventArgs e)
        {
            PasswordTextBox.PasswordChar = ShowHidePasswordCheckBox.Checked ? '*' : '\0';
        }
        /// <summary>
        /// Perform login
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void LoginButton_Click(object sender, EventArgs e)
        {
 
            if (!string.IsNullOrWhiteSpace(UserNameTextBox.Text) && !string.IsNullOrWhiteSpace(PasswordTextBox.Text))
            {
 
                /*
                 * Change server if not using SQLEXPRESS
                 */
                var ops = new DatabaseUser(".\\SQLEXPRESS", "UserLoginExample");
 
                var encryption = new Encryption();
                // encrypt user name and password
                var userNameBytes = encryption.Encrypt(UserNameTextBox.Text, "111");
                var passwordBytes = encryption.Encrypt(PasswordTextBox.Text, "111");
 
                var results = ops.SqlCredentialLogin(userNameBytes, passwordBytes);
 
                //
                // Login recognized (does not know if the user has proper permissions to the tables at this point)
                //
                if (results.Success)
                {
                    Hide();
                    var mainForm = new MainForm(userNameBytes, passwordBytes);
                    mainForm.ShowDialog();
                }
                else
                {
                    MessageBox.Show(results.Message);
                }
            }
            else
            {
                MessageBox.Show("Incomplete information to continue.");
            }
        }
        private void CancelButton_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

Quick start


Some developers may want a solution which has all needed code in a single Windows Form project rather than rely on a class project especially for new developers with little experience working with multiple projects. The following project has all code required to perform a secure login with minimal code.

The login form may be both main form and login form, if this is the case simply rename the LoginForm to MainForm, otherwise keep Login form as the main opening form and upon successful login open the true main form.


Running code samples

  • Download the Visual Studio solution, open and build
  • Run script.sql found at the root of the solution
  • Make sure user name and password are properly setup (they should be as they are created in the script).
  • Run the program.

Summary

This article has provided code to provide a login to your SQL-Server database when Windows authentication is not an option for Window form projects.

Resources

Create user login (Code Project) 

See also

TechNet: SQL Server Logins: Back To Basics - learn about creating logins

TechNet: SQL Server: Database Engine Permission Basics 

Source code

The following GitHub repository contains all code and database scripts to try out the login processes.