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
Before writing any code users must be created for the intended database along with permissions to each table the user will need access too.
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 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]
USER
KarenPayne
FOR
LOGIN KarenPayne;
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.
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
State ,
permission_name
[Permission] ,
obj.
name
[
on
Object] ,
dPrinc.
to
User
Name
] ,
sPrinc.
[who
is
Login
]
FROM
sys.database_permissions
perms
JOIN
sys.database_principals
dPrinc
ON
perms.grantee_principal_id = dPrinc.principal_id
sys.objects
obj
perms.major_id = obj.object_id
LEFT
OUTER
sys.server_principals
sPrinc
dPrinc.sid = sPrinc.sid
WHERE
= @LoginName;
Managing sensitive login details
using
System;
System.IO;
System.Security.Cryptography;
System.Text;
namespace
LoginLibrary.SecurityClasses
{
SecurityClasses
/// <summary>
/// Simple encryption decryption of strings
/// </summary>
public
class
Encryption
byte
[] Encrypt(
string
plainText,
secretKey)
[] encryptedPassword =
null
;
(var outputStream =
new
MemoryStream())
RijndaelManaged algorithm = getAlgorithm(secretKey);
(var cryptoStream =
CryptoStream(outputStream,
algorithm.CreateEncryptor(), CryptoStreamMode.Write))
[] inputBuffer = Encoding.Unicode.GetBytes(plainText);
cryptoStream.Write(inputBuffer, 0, inputBuffer.Length);
cryptoStream.FlushFinalBlock();
encryptedPassword = outputStream.ToArray();
}
return
encryptedPassword;
Decrypt(
[] encryptedBytes,
plainText =
(var inputStream =
MemoryStream(encryptedBytes))
CryptoStream(inputStream,
algorithm.CreateDecryptor(), CryptoStreamMode.Read))
[] outputBuffer =
[((
int
)(inputStream.Length - 1)) + 1];
readBytes = cryptoStream.Read(outputBuffer, 0, (
)inputStream.Length);
plainText = Encoding.Unicode.GetString(outputBuffer, 0, readBytes);
plainText;
private
RijndaelManaged getAlgorithm(
const
salt =
"akl~jdf"
keySize = 256;
var keyBuilder =
Rfc2898DeriveBytes(secretKey, Encoding.Unicode.GetBytes(salt));
var algorithm =
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;
algorithm;
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 =
Encryption();
// encrypt user name and password
var userNameBytes = encryption.Encrypt(UserNameTextBox.Text,
"111"
);
var passwordBytes = encryption.Encrypt(PasswordTextBox.Text,
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.
LoginLibrary.SupportClasses
SupportClasses
SqlServerLoginResult
bool
Success {
get
set
;}
Failed => Success ==
false
GenericException {
Message {
override
ToString()
Message;
System.Data.SqlClient;
LoginLibrary.SecurityClasses.SecurityClasses;
LoginLibrary.SupportClasses.SupportClasses;
LoginLibrary.DataClasses
DataClasses
/// Responsible to validating a user has permissions
/// to access the database, not tables.
DatabaseUser
serverName;
catalogName;
DatabaseUser(
pServerName,
pCatalogName)
serverName = pServerName;
catalogName = pCatalogName;
SqlServerLoginResult Login(
[] pNameBytes,
[] pPasswordBytes)
var loginResult =
SqlServerLoginResult();
var secureOperations =
var userName = secureOperations.Decrypt(pNameBytes,
var userPassword = secureOperations.Decrypt(pPasswordBytes,
ConnectionString =
$
"Data Source={serverName};"
+
"Initial Catalog={catalogName};"
"User Id={userName};Password={userPassword};"
"Integrated Security=False"
(var cn =
SqlConnection {ConnectionString = ConnectionString})
try
cn.Open();
loginResult.Success =
true
catch
(SqlException failedLoginException) when (failedLoginException.Number == 18456)
loginResult.GenericException =
loginResult.Message =
"Can not access data."
(SqlException genericSqlException)
(Exception ex)
loginResult.Message = ex.Message;
loginResult;
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 =
MainForm(userNameBytes, passwordBytes);
mainForm.ShowDialog();
else
MessageBox.Show(results.Message);
System.Windows.Forms;
LoginLibrary.DataClasses.DataClasses;
LoginUserInterface
partial
MainForm
readonly
[] _userNameBytes;
[] _userPasswordBytes;
BindingSource _productBindingSource =
BindingSource();
MainForm(
InitializeComponent();
_userNameBytes = pNameBytes;
_userPasswordBytes = pPasswordBytes;
void
MainForm_Load(
object
sender, EventArgs e)
var ops =
DataOperations(
_userNameBytes,
_userPasswordBytes,
"KARENS-PC"
,
"UserLoginExample"
var productTable = ops.ReadProductsByCategory(1);
(ops.IsSuccessFul)
_productBindingSource.DataSource = productTable;
ProductsDataGridView.DataSource = _productBindingSource;
MessageBox.Show($
"Encountered issues: {ops.LastExceptionMessage}"
MainFormClosed(
sender, FormClosedEventArgs e)
Application.ExitThread();
System.Data;
System.Linq;
SupportLibrary;
DataOperations : BaseExceptionProperties
ConnectionString;
[] pPasswordBytes,
"Data Source={pServerName};Initial Catalog={pCatalogName};"
"User Id={secureOperations.Decrypt(pNameBytes, "
111
")};"
"Password={secureOperations.Decrypt(pPasswordBytes, "
Console.WriteLine();
/// 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.
/// <param name="pCategoryIdentifier"></param>
/// <returns></returns>
DataTable ReadProductsByCategory(
pCategoryIdentifier)
mHasException =
var productDataTable =
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); "
(var cmd =
SqlCommand {Connection = cn})
cmd.Parameters.AddWithValue(
"@CategoryID"
, pCategoryIdentifier);
cmd.CommandText = selectStatement;
productDataTable.Load(cmd.ExecuteReader());
var identifiers = productDataTable
.Columns.Cast<DataColumn>()
.Where((column) => column.ColumnName.EndsWith(
"ID"
))
.Select((item) => item.ColumnName).ToList();
foreach
(
columnName
in
identifiers)
productDataTable.Columns[columnName].ColumnMapping = MappingType.Hidden;
mLastException = ex;
productDataTable;
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.
Program
static
Main(
[] args)
SqlConnection.ChangePassword(
"Data Source=a_server;Initial Catalog=a_database;UID=user;PWD=old_password"
"new_password"
SqlServerLoginResult SqlCredentialLogin(
connectionString = $
var securePassword =
SecureString();
(var character
userPassword)
securePassword.AppendChar(character);
securePassword.MakeReadOnly();
var credentials =
SqlCredential(userName, securePassword);
SqlConnection {ConnectionString = connectionString})
cn.Credential = credentials;
SqlCredentialLoginInterface
LoginForm
LoginForm()
ShowHidePasswordCheckBox_CheckedChanged(
PasswordTextBox.PasswordChar = ShowHidePasswordCheckBox.Checked ?
'*'
:
'\0'
/// Perform login
/// <param name="sender"></param>
/// <param name="e"></param>
LoginButton_Click(
(!
.IsNullOrWhiteSpace(UserNameTextBox.Text) && !
.IsNullOrWhiteSpace(PasswordTextBox.Text))
/*
* Change server if not using SQLEXPRESS
*/
".\\SQLEXPRESS"
var results = ops.SqlCredentialLogin(userNameBytes, passwordBytes);
MessageBox.Show(
"Incomplete information to continue."
CancelButton_Click(
Close();
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.
Create user login (Code Project)
The following GitHub repository contains all code and database scripts to try out the login processes.