How to create MSSQL Server Encrypted Connections?

Scriptcase 9 General
#1

Hi,

There doesn’t seem to be a way to create encrypted MS SQL Server connections natively within scriptcase as you can with say MySQL. The option in scriptcase simply doesn’t exist like it does for MySQL. Is it possible to do this without creating an ODBC connection? PDO allows you to create connection info which enables encryption so theoretically it’s possible…

Our business is strict on secure development so we have to make everything is secure and encrypted where possible.

I can achieve encryption if I use the SQL Server ODBC driver by enabling “use strong encryption for data” but it’s adding an extra level of complexity and then another level for docker containers, CI pipelines and so on… it’d be better if it could be handled centrally within scriptcase.

Any tips would be appreciated.

thanks,
Arthur

#2

I’m not an expert but as far as I know are the database and connections between the database driver and the database encrypted by default. It is possible to encrypt individual fields as well, but to access those, you need to work from within Visual Studio (.Net) as you can’t get it to work simply in other connections like those in PHP or Java. That is different with encryption on MySQL/mariaDB. That implementation is fully transparent. You set up the certificates and then the database is encrypted. You don’t have to specify that in your code or connection.

#3

Thanks so much for your response. I’m really pleased to see how active and responsive this forum is.

I’ve tested if connections are encrypted and they arent. You can test this easily using

SELECT session_id,encrypt_option FROM sys.dm_exec_connections

When I use an ODBC connection that I’ve set as encrypted, I get true, but the default scriptcase SQL Server connections I get false every time.

I’ve sent a request to scriptcase to add this as a feature. I really hope they add it as this will tick a very significant box for us. It will also give peace of mind to all others that may have thought sql server connections are encrypted by default.

thanks,
Arthur

1 Like
#4

As far as i know,
Connections with sql azure are always encrypted.
Did you try an azure database connector ?
There are also sql server settings for on premise to accept only encrypted , / my guess.

1 Like
#5

Thanks Maximinl !

I’ve looked and there is an option in SQL server on premise to force encryption. I’ll try it when I get a bit of time, I’m sure it will work. I don’t want to just change the setting just yet as I’m hosting several databases and I don’t want to risk breaking anything!

Configure SQL Server Database Engine for encryption - SQL Server | Microsoft Learn

1 Like
#6

Hi All,

I thought I’d make a mention of this. Scriptcase have now implemented encrypted connections to SQL Server as of version 9.9.021. It’s been added to the connection settings! This is great news!

image

1 Like
#7

SQL Azure server connections are always encrypted (similar to force setting on premise), and it worked without these security settings change, i wonder what it really does?

#8

hi maximnl,

I don’t have any experience with Azure SQL databases but it’s good to know those connections are encrypted as we are going to be moving to the cloud at some point.

With local SQL server instances when creating a PDO connection / connection string you can tell it to use encryption via the connection string.

Encrypt=true;

If your cert is not from a trusted CA.you can also add,

TrustServerCertificate=true

You’ll end up with something like,

$pdo = new \PDO("sqlsrv:Server=myserver;Database=databasename;Encrypt=true;", $username, $password);

You can find the options available in the PDO driver here Connection Options - PHP drivers for SQL Server | Microsoft Learn

Scriptcase appear to have implemented this… probably because I asked them :wink: via a request!