Need some help with Windows authentication

Dwipanjan 1 Reputation point
2020-12-16T08:32:42.787+00:00

Hello everyone,

I need some help with Windows authentication.

We have a C++ application running on a RHEL server which uses the MS ODBC driver 17 to connect to a SQL Server DB running on Windows. At this moment, we use SQL authentication. We have now been asked to use Windows authentication. Towards that, we have made some changes related to Kerberos and also installed the PAM libraries. With those changes, using the kinit command with a service account, we were able to use the sqlcmd utility to connect to the SQL Server database as below:

$kinit SvcAcct_PERFNR@XXXDEV.LOCAL
Password for SvcAcct_PERFNR@XXXDEV.LOCAL:
$sqlcmd -S vwmazxaminsql01.XXXDEV.local,2431
1> select SUSER_NAME()
2> go


XXXDEV\SvcAcct_PERFNR

(1 rows affected)

However, the application uses SQLConnect API which does not seem to be working even after those changes.I then tried working with the isql utility too but got the same error which was "could not SQLConnect". Any help with this would be seriously helpful.

This is how the odbcinst.ini file looks like

$cat /etc/odbcinst.ini

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1
Threading=1
UsageCount=1

odbc.ini file

$cat /etc/odbc.ini

[XAMINCLSQLDEV01]
Driver = ODBC Driver 17 for SQL Server
Server = vwmazxaminsql01.xxxdev.local,2431
Trusted_Connection = yes

Also explored the following entries in the odbc.ini file but to no avail.

[XAMINCLSQLDEV01]
Driver=ODBC Driver 17 for SQL Server
Server=vwmazxaminsql01.xxxdev.local,2431
Trusted_Connection=true
ServerSPN=SvcAcct_PERFNR/vwmazxaminsql01.xxxdev.local:2431

and

[XAMINCLSQLDEV01]
Driver=ODBC Driver 17 for SQL Server
Server=XX.X.XXX.XXX,2431
Domain=xxxdev.local
integratedSecurity=true
AuthenticationMethod=4
Trusted_Connection=yes
ServerSPN=SvcAcct_PERFNR/vwmazxaminsql01.xxxdev.local:2431

This is the error that I get.

$ kinit SvcAcct_PERFNR@XXXDEV.LOCAL
Password for SvcAcct_PERFNR@XXXDEV.LOCAL:
$ /usr/bin/isql -v XAMINCLSQLDEV01
[28000][unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user ''.
[ISQL]ERROR: Could not SQLConnect

This is the error before Kerberos setup was done.

$isql -v XAMINCLSQLDEV01
[S1000][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: Server not found in Kerberos database
[S1000][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Cannot generate SSPI context
[ISQL]ERROR: Could not SQLConnect

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,785 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,576 questions
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.