Microsoft SQL Server is a popular relational database management system developed by Microsoft. SQL Server supports various editions tailored for different workloads, from small applications to large enterprise solutions. It uses SQL (Structured Query Language) for querying and managing databases and offers various features.
In the following blog we will see how we can connect a dotnet application to an SQL Server (with kerberos authentication) on IBM Z (RHEL) .
Pre-Requisites:-
Windows SQL Server 2022 with Active Directory Services Enabled
Recommend to use the latest dotnet SDK
Microsoft.Data.SqlClient 5.2-preview4+
- Installing dependencies and packages
sudo dnf install krb5-workstation krb5-libs sssd realmd
2. Discover the domain name
In order for the machine to detect the dns we need to add the domain controller's IP to /etc/resolv.conf, then we should be able to discover the realm.
For demonstration purposes we have used our domain name as SQL.COM
[root@mymachine ~]# realm discover sql.com
sql.com
type: kerberos
realm-name: SQL.COM
domain-name: sql.com
configured: kerberos-member
server-software: active-directory
client-software: sssd
required-package: oddjob
required-package: oddjob-mkhomedir
required-package: sssd
required-package: adcli
required-package: samba-common-tools
login-formats: %U@sql.com
login-policy: allow-realm-logins
3. Now we can join the realm
make sure you join the Administrator user so that it has correct permissions to add you to the realm
[root@mymachine ~]# realm join -v -U Administrator sql.com
* Resolving: _ldap._tcp.sql.com
* Performing LDAP DSE lookup on: 172.0.0.1
* Successfully discovered: sql.com
Password for Administrator:
* Required files: /usr/sbin/oddjobd, /usr/libexec/oddjob/mkhomedir, /usr/sbin/sssd, /usr/sbin/adcli
* LANG=C /usr/sbin/adcli join --verbose --domain sql.com --domain-realm SQL.COM --domain-controller 172.0.0.1 --login-type user --login-user Administrator --stdin-password
* Using domain name: sql.com
* Calculated computer account name from fqdn: mymachine
* Using domain realm: sql.com
* Sending NetLogon ping to domain controller: 172.0.0.1
* Received NetLogon info from: WIN-CI7DKPOF47U.sql.com
* Wrote out krb5.conf snippet to /var/cache/realmd/adcli-krb5-4GL4Yv/krb5.d/adcli-krb5-conf-O4HS48
* Authenticated as user: Administrator@SQL.COM
* Using GSS-SPNEGO for SASL bind
* Looked up short domain name: SQL
* Looked up domain SID: S-1-5-21-2288060879-3932693953-3579829357
* Received NetLogon info from: WIN-CI7DKPOF47U.sql.com
* Using fully qualified name: mymachine
* Using domain name: sql.com
* Using computer account name: mymachine
* Using domain realm: sql.com
* Calculated computer account name from fqdn: mymachine
* Generated 120 character computer password
* Using keytab: FILE:/etc/krb5.keytab
* A computer account for mymachine$ does not exist
* Found well known computer container at: CN=Computers,DC=sql,DC=com
* Calculated computer account: CN=mymachine,CN=Computers,DC=sql,DC=com
* Encryption type [16] not permitted.
* Encryption type [23] not permitted.
* Encryption type [3] not permitted.
* Encryption type [1] not permitted.
* Created computer account: CN=mymachine,CN=Computers,DC=sql,DC=com
* Trying to set computer password with Kerberos
* Set computer password
* Retrieved kvno '2' for computer account in directory: CN=mymachine,CN=Computers,DC=sql,DC=com
* Checking RestrictedKrbHost/mymachine
* Added RestrictedKrbHost/mymachine
* Checking RestrictedKrbHost/mymachine
* Added RestrictedKrbHost/mymachine
* Checking host/mymachine
* Added host/mymachine
* Checking host/mymachine
* Added host/mymachine
* Discovered which keytab salt to use
* Added the entries to the keytab: mymachine$@SQL.COM: FILE:/etc/krb5.keytab
* Added the entries to the keytab: host/mymachine@SQL.COM: FILE:/etc/krb5.keytab
* Added the entries to the keytab: host/mymachine@SQL.COM: FILE:/etc/krb5.keytab
* Added the entries to the keytab: RestrictedKrbHost/mymachine@SQL.COM: FILE:/etc/krb5.keytab
* Added the entries to the keytab: RestrictedKrbHost/mymachine@SQL.COM: FILE:/etc/krb5.keytab
* /usr/bin/systemctl enable sssd.service
Created symlink /etc/systemd/system/multi-user.target.wants/sssd.service → /usr/lib/systemd/system/sssd.service.
* /usr/bin/systemctl restart sssd.service
* /usr/bin/sh -c /usr/bin/authselect select sssd with-mkhomedir --force && /usr/bin/systemctl enable oddjobd.service && /usr/bin/systemctl start oddjobd.service
Backup stored at /var/lib/authselect/backups/2023-12-12-15-18-28.imKbuH
Profile "sssd" was selected.
The following nsswitch maps are overwritten by the profile:
- passwd
- group
- netgroup
- automount
- services
Make sure that SSSD service is configured and enabled. See SSSD documentation for more information.
- with-mkhomedir is selected, make sure pam_oddjob_mkhomedir module
is present and oddjobd service is enabled and active
- systemctl enable --now oddjobd.service
* Successfully enrolled machine in realm
4. Use kinit to authenticate the machine to use Active Directory Services
[root@mymachine ~]# kinit
Password for root@SQL.COM:
Warning: Your password will expire in 2 days on Fri 15 Dec 2023 08:01:41 AM CET
[root@mymachine ~]#
5. Create a new dotnet console application
dotnet new console -n Hello && cd Hello
6. Add the Microsoft.Data.SqlClient NuGet package.
Recently the DotNet Compiler Team added support for Big Endian Systems to the SqlClient Driver and this has been added with the 5.2.0-preview4 release
dotnet add package Microsoft.Data.SqlClient --version 5.2.0-preview4.23342.2
7. Lets write a sample program which inserts an Hello World in Xml format and later reads it back to the client.
using Microsoft.Data.SqlClient;
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Xml;
public class Hello
{
public static void Main(string []args)
{
string tempTable = "xml_" + Guid.NewGuid().ToString().Replace('-', '_');
string initStr = "create table " + tempTable + " (xml_col XML)";
string insertNormStr = "INSERT " + tempTable + " VALUES('<doc>Hello World</doc>')";
string queryStr = "select * from " + tempTable;
using (SqlConnection conn = new SqlConnection("Server=WIN-6KRA9FF9IPI.sql.com;Database=Northwind;Trusted_Connection=True;TrustServerCertificate=True;"))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = initStr;
cmd.ExecuteNonQuery();
try
{
cmd.CommandText = insertNormStr;
cmd.ExecuteNonQuery();
cmd.CommandText = queryStr;
using (SqlDataReader reader = cmd.ExecuteReader())
{
string[] expectedValues =
{
"<doc>Hello World</doc>"
};
while (reader.Read())
{
SqlXml sx = reader.GetSqlXml(0);
XmlReader xr = sx.CreateReader();
xr.Read();
Console.WriteLine(xr.ReadOuterXml());
}
}
}
finally
{
cmd.CommandText = "drop table " + tempTable;
cmd.ExecuteNonQuery();
}
}
}
}
8. Lets build and execute the sample program.
[root@mymachine Hello]# dotnet build -c Release
MSBuild version 17.4.8+6918b863a for .NET
Determining projects to restore...
All projects are up-to-date for restore.
Hello -> /root/Hello/bin/Release/net7.0/Hello.dll
Build succeeded.
0 Warning(s)
0 Error(s)
Time Elapsed 00:00:02.16
[root@t8375026 Hello]# dotnet bin/Release/net7.0/Hello.dll
<doc>Hello World</doc>
[root@t8375026 Hello]#