Introduction

Usually opening the connection to Microsoft SQL-Server database involves no security configuration for small personal applications, since we use the authentication of the person who executes 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. 

C# Version of this article

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 the script were the script may be in SSMS or in a new query window created in Visual Studio 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 traveling 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.

Imports System.IO
Imports System.Security.Cryptography
Imports System.Text
 
Namespace SecurityClasses
    ''' <summary>
    ''' Simple encryption decryption of strings
    ''' </summary>
    Public Class Encryption
        Public Function Encrypt(plainText As String, secretKey As String) As Byte()
            Dim encryptedPassword As Byte()
            Using outputStream = New MemoryStream()
                Dim algorithm As RijndaelManaged = getAlgorithm(secretKey)
                Using cryptoStream =
                    New CryptoStream(outputStream, algorithm.CreateEncryptor(),
                                     CryptoStreamMode.Write)
                    Dim inputBuffer() As Byte = Encoding.Unicode.GetBytes(plainText)
                    cryptoStream.Write(inputBuffer, 0, inputBuffer.Length)
                    cryptoStream.FlushFinalBlock()
                    encryptedPassword = outputStream.ToArray()
                End Using
            End Using
            Return encryptedPassword
        End Function
 
        Public Function Decrypt(encryptedBytes As Byte(), secretKey As String) As String
            Dim plainText As String = Nothing
            Using inputStream = New MemoryStream(encryptedBytes)
                Dim algorithm As RijndaelManaged = getAlgorithm(secretKey)
                Using cryptoStream =
                    New CryptoStream(inputStream, algorithm.CreateDecryptor(),
                                     CryptoStreamMode.Read)
                    Dim outputBuffer(0 To CType(inputStream.Length - 1, Integer)) As Byte
                    Dim readBytes As Integer =
                            cryptoStream.Read(outputBuffer, 0, CType(inputStream.Length, Integer))
                    plainText = Encoding.Unicode.GetString(outputBuffer, 0, readBytes)
                End Using
            End Using
            Return plainText
        End Function
        Private Function getAlgorithm(secretKey As String) As RijndaelManaged
            Const salt As String = "akl~jdf"
            Const keySize As Integer = 256
 
            Dim keyBuilder = New Rfc2898DeriveBytes(secretKey, Encoding.Unicode.GetBytes(salt))
            Dim algorithm = New RijndaelManaged()
            algorithm.KeySize = keySize
            algorithm.IV = keyBuilder.GetBytes(CType(algorithm.BlockSize / 8, Integer))
            algorithm.Key = keyBuilder.GetBytes(CType(algorithm.KeySize / 8, Integer))
            algorithm.Padding = PaddingMode.PKCS7
            Return algorithm
        End Function
    End Class
End Namespace

 

Login window

This window/form consist of a TextBox for user name, a TextBox for user password was 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.

Dim encryption = New Encryption
' encrypt user name and password
Dim userNameBytes = encryption.Encrypt(UserNameTextBox.Text, "111")
Dim passwordBytes = encryption.Encrypt(PasswordTextBox.Text, "111")

 

This is followed by creating an instance of the DatabaseUser class which attempts to connect 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 the 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 SupportClasses
    Public Class SqlServerLoginResult
        Public Property Success() As Boolean
        Public ReadOnly Property Failed() As Boolean
            Get
                Return Success = False
            End Get
        End Property
        Public Property GenericException() As Boolean
        Public Property Message() As String
 
        Public Overrides Function ToString() As String
            Return Message
        End Function
    End Class
End Namespace

 

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.

Imports System.Data.SqlClient
Imports LoginLibrary.SecurityClasses
Imports LoginLibrary.SupportClasses
 
Namespace DataClasses
    ''' <summary>
    ''' Responsible to validating a user has permissions
    ''' to access the database, not tables.
    ''' </summary>
    Public Class DatabaseUser
        Private serverName As String
        Private catalogName As String
        Public Sub New(pServerName As String, pCatalogName As String)
            serverName = pServerName
            catalogName = pCatalogName
        End Sub
        Public Function Login(pNameBytes As Byte(), pPasswordBytes As Byte()) As SqlServerLoginResult
            Dim loginResult = New SqlServerLoginResult
 
            Dim secureOperations = New Encryption
            Dim userName = secureOperations.Decrypt(pNameBytes, "111")
            Dim userPassword = secureOperations.Decrypt(pPasswordBytes, "111")
 
 
            Dim ConnectionString As String =
                    $"Data Source={serverName};" &
                    $"Initial Catalog={catalogName};" &
                    $"User Id={userName};Password={userPassword};" &
                    "Integrated Security=False"
 
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Try
                    cn.Open()
                    loginResult.Success = True
                Catch failedLoginException As SqlException When failedLoginException.Number = 18456
                    loginResult.Success = False
                    loginResult.GenericException = False
                    loginResult.Message = "Can not access data."
                Catch genericSqlException As SqlException
                    loginResult.Success = False
                    loginResult.GenericException = False
                    loginResult.Message = "Can not access data."
                Catch ex As Exception
                    loginResult.Success = False
                    loginResult.GenericException = True
                    loginResult.Message = ex.Message
                End Try
            End Using
 
            Return loginResult
 
        End Function
    End Class
End Namespace

 

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).

Dim 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 Then
    Hide()
    Dim mainForm As New MainForm(userNameBytes, passwordBytes)
    mainForm.ShowDialog()
Else
    MessageBox.Show(results.Message)
End If

 

In the main form, constructor encrypted the 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).

Imports LoginLibrary.DataClasses
 
Public Class MainForm
 
    Private userNameBytes As Byte()
    Private userPasswordBytes As Byte()
 
    Private ProductBindingSource As New BindingSource
 
    Public Sub New(pNameBytes As Byte(), pPasswordBytes As Byte())
 
        InitializeComponent()
 
        userNameBytes = pNameBytes
        userPasswordBytes = pPasswordBytes
 
    End Sub
    Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 
        Dim ops As New DataOperations(
            userNameBytes,
            userPasswordBytes,
            "KARENS-PC",
            "UserLoginExample")
 
        Dim productTable = ops.ReadProductsByCategory(1)
        If ops.IsSuccessFul Then
            ProductBindingSource.DataSource = productTable
            ProductsDataGridView.DataSource = ProductBindingSource
        Else
            MessageBox.Show($"Encountered issues: {ops.LastExceptionMessage}")
        End If
 
    End Sub
    Private Sub MainFormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        Application.ExitThread()
    End Sub
End Class

 

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.

Imports System.Data.SqlClient
Imports LoginLibrary.SecurityClasses
Imports SupportLibrary
 
Namespace DataClasses
 
    Public Class DataOperations
        Inherits BaseExceptionProperties
 
        Private ConnectionString As String
        '
        Public Sub New(
            pNameBytes As Byte(),
            pPasswordBytes As Byte(),
            pServerName As String,
            pCatalogName As String)
 
            Dim secureOperations = New Encryption
 
            ConnectionString = $"Data Source={pServerName};Initial Catalog={pCatalogName};" &
                               $"User Id={secureOperations.Decrypt(pNameBytes, "111")};" &
                               $"Password={secureOperations.Decrypt(pPasswordBytes, "111")};" &
                               "Integrated Security=False"
 
        End Sub
        ''' <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 Function ReadProductsByCategory(pCategoryIdentifier As Integer) As DataTable
 
            mHasException = False
 
            Dim productDataTable As New DataTable
 
            Dim selectStatement =
                    <SQL>
                    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 );
                    </SQL>.Value
 
 
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
 
                    cmd.Parameters.AddWithValue("@CategoryID", pCategoryIdentifier)
                    cmd.CommandText = selectStatement
 
                    Try
                        cn.Open()
                        productDataTable.Load(cmd.ExecuteReader())
 
                        Dim identifiers = productDataTable.
                                Columns.Cast(Of DataColumn).
                                Where(Function(column) column.ColumnName.EndsWith("ID")).
                                Select(Function(item) item.ColumnName).ToList()
 
                        For Each columnName As String In identifiers
                            productDataTable.Columns(columnName).ColumnMapping = MappingType.Hidden
                        Next
 
                    Catch ex As Exception
                        mHasException = True
                        mLastException = ex
                    End Try
                End Using
            End Using
 
            Return productDataTable
 
        End Function
    End Class
End Namespace

 

 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.

Module Module1 
    Sub Main() 
System.Data.SqlClient.SqlConnection.ChangePassword( 
        "Data Source=a_server;Initial Catalog=a_database;UID=user;PWD=old_password",  
       "new_password"
    End Sub 
End Module

 

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.

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.

Alternate Login Framework 4.5 or higher

SqlCredential class 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 user name 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.

Keeping in mind how the first login above worked using SqlCredentials method works similarly for accepting username and password. The username and password are encrypted, passed to a method below.

Public Function SqlCredentialLogin(pNameBytes As Byte(), pPasswordBytes As Byte()) As SqlServerLoginResult

Decryption is done the same as in the first login method Login. Next, the password is set into a SecureString.
Dim securePassword = New SecureString()
 
For Each character In userPassword
    securePassword.AppendChar(character)
Next
 
securePassword.MakeReadOnly()

Create a new instance of SqlCredential.
Dim credentials = New SqlCredential(userName, securePassword)

Assign credentials variable to Credential property of the Connection.
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
    Try
        cn.Credential = credentials
        cn.Open()

The entire method 
Public Function SqlCredentialLogin(pNameBytes As Byte(), pPasswordBytes As Byte()) As SqlServerLoginResult
    Dim loginResult = New SqlServerLoginResult
    Dim secureOperations = New Encryption
 
    Dim userName = secureOperations.Decrypt(pNameBytes, "111")
    Dim userPassword = secureOperations.Decrypt(pPasswordBytes, "111")
 
    Dim ConnectionString As String =
            $"Data Source={serverName};" &
            $"Initial Catalog={catalogName};"
 
 
    Dim securePassword = New SecureString()
 
    For Each character In userPassword
        securePassword.AppendChar(character)
    Next
 
    securePassword.MakeReadOnly()
 
    Dim credentials = New SqlCredential(userName, securePassword)
 
    Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
        Try
            cn.Credential = credentials
            cn.Open()
            loginResult.Success = True
        Catch failedLoginException As SqlException When failedLoginException.Number = 18456
            loginResult.Success = False
            loginResult.GenericException = False
            loginResult.Message = "Can not access data."
        Catch genericSqlException As SqlException
            loginResult.Success = False
            loginResult.GenericException = False
            loginResult.Message = "Can not access data."
        Catch ex As Exception
            loginResult.Success = False
            loginResult.GenericException = True
            loginResult.Message = ex.Message
        End Try
 
    End Using
 
    Return loginResult
 
End Function

Which method to select is going to be dependent on Framework version a project uses or business requirements.

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

How to create user login (Code Project) 

See also

SQL Server database login for Windows Forms (C#)
SQL Server Logins: Back To Basics - learn about creating logins
SQL Server: Database Engine Permission Basics 

Source code

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