Stuff I'm Up To

Technical Ramblings

Linux and MS SQL ODBC Authentication — May 16, 2019

Linux and MS SQL ODBC Authentication

My recent troubles spawned from connecting my Laravel application to a Microsoft SQL Server database. My username and password are correct but the connection fails.

In the storage/logs/laravel.log file I found the following:

[2019-05-16 08:22:46] work.ERROR: SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) (SQL: select * from holidays where holiday_date > '2019-05-16 08:22:46') {"exception":"[object] (Illuminate\Database\QueryException(code: HY000): SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) (SQL: select * from holidays where holiday_date > '2019-05-16 08:22:46') at /PATH/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: HY000): SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) at /PATH/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:47, PDOException(code: HY000): SQLSTATE[HY000]: [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1000) at /PATH/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:43)

I haven’t setup any Kerberos on my Linux side to authenticate and it would appear that my MS SQL wants me to use that. So instead of setting up Kerberos I opted for getting the ODBC connection NOT to use Kerberos.

To do this for my specific user account on my development system I created an ~/.odbc.ini file and added details to disable Kerberos:

.odbc.ini

[my_database]
 Driver = ODBC Driver 17 for SQL Server
 Server = myserver.domain.local,1433
 Database = my_database
# If NOT using Kerberos authentication:
 Trusted_Connection = No
 ServerSPN = MSSQLSvc/myserver.domain.local:my_database
# If using SSL encryption:
 Encryption = Yes
# If using SSL and not importing the server certificate into your certificate store:
 TrustServerCertificate = Yes

To ensure that Laravel makes use of this config the only way I could do it was to NOT use the eloquent models and had to use PHP PDO directly.

// We need to use a direct PDO here because of the
// Trusted Connection=No / SSPI required in .odbc.ini
$pdo = new \PDO('sqlsrv:Server='.env('DB_HOST'),
  env('DB_USER'), env('DB_PASSWORD'));

// Create the number of token parameters
$params = implode(',', array_fill(0, count($tokens), '?'));
$sql = "select data from my_table where something in ($params);";
$stmt = $pdo->prepare($sql);
$stmt->execute($tokens);

$rows = $stmt->fetchAll(\PDO::FETCH_ASSOC); // Associated Array only

Advertisements
MSSQL ACCEPT_EULA — May 1, 2019

MSSQL ACCEPT_EULA

My automated apt updates failed to update the Microsoft SQL components because they don’t accept the terms and condition in the EULA.

To resolve this I added the following line in /etc/environment that allows the EULA to be accepted by default.

ACCEPT_EULA=Y

DBeaver – SQL GUI — November 6, 2018

DBeaver – SQL GUI

I’ve used a few SQL GUI’s over the years, SQuirreL, DBVisualizer, HeidiSQL, MySQL Workbench, but the one that stands out recently is DBeaver.

It’s got a community and enterprise edition. The community does everything I need and connects to all the SQL servers we use, Microsoft SQL, MySQL, Postgres/PostGIS.

Being Java based it’s cross platform, so you can use it in Windows too.

PHP7.2 and MSSQL Drivers — February 5, 2018

PHP7.2 and MSSQL Drivers

I upgraded to PHP v7.2 on my Debian Buster/Sid today. Not a problem until I realised I’d broken my Microsoft SQL Drivers.

The real reason for my update from v7.0 to v7.2 was down to a problem I suffered with some Laravel console commands I was working on. When I ran a CLI based command php artisan group:command, which is a command I’m writing that uses a model from an MSSQL server. It would come up with an error message:

In Connection.php line 664:
                                                                               
  could not find driver (SQL: select top 1 * from [vwContract] order by [Ref]  
   asc)                                                                        
                                                                               
In Connector.php line 67:
                         
  could not find driver  

Continue reading

PHP7.0, Microsoft SQL Driver & Debian (stretch) — December 12, 2017

PHP7.0, Microsoft SQL Driver & Debian (stretch)

What a mission today has been. I think I’ll ultimately roll back to using Debian Jessie as Stretch isn’t a supported system, yet.

To get the MS SQL ODBC driver working even in Jessie appears to be a challenge. In Stretch I almost surrendered. It is working, but I do think it’s a bit of a hack as I’ve had to install an older libssl1.0.0 and enable the locale en_US.UTF-8.

As it’s downloaded using apt over https you’ll need to add in the apt-transport-https package to your system:

$ sudo apt-get install apt-transport-https

Or you will get this error

Reading package lists… Done                     
E: The method driver /usr/lib/apt/methods/https could not be found.
N: Is the package apt-transport-https installed?
E: Failed to fetch https://packages.microsoft.com/debian/9/prod/dists/stretch/InRelease
E: Some index files failed to download. They have been ignored, or old ones used instead.

PHP development voted out the inclusion of MS SQL to the project so now you must compile and install it yourself. There are some very good instructions out there to help you – even from Microsoft.

https://docs.microsoft.com/en-gb/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server

Continue reading
MSSQL Server SSL Certificate — October 5, 2016

MSSQL Server SSL Certificate

Having updated the CA certificate it’s time to start rolling out the new SHA-256 algorithm to the other Windows servers. Group Policy (GPO) takes care of the new CA certificate distribution and the clients and servers are getting that in their Trusted Root stores automatically. But the servers have a range of certificate expiry dates and won’t  expire for some time. So to satisfy the vulnerability scan results we’re having to update each server as we get to them.

This means visiting each server running MMC, adding in the Certificate Snap-in for the Local Computer and then renewing the certificate(s). Once that’s done it’s a case of telling the applications to use the new certificate.

Typically this means choosing the certificate in the Terminal Services Session Host management console, setting IIS to use the new certificate and updating SQL so that uses the new certificate too.

Continue reading