February 22, 2017 at 11:48 am
I've been having a discussion with another DBA at my company about setting up an encrypted connection between SSIS and a remote SQL Server. We can connect fine regularly through Management Studio and SSIS, but the point of contention is from this article:
https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-microsoft-management-console
The initial test the other DBA setup used the method described in the Summary, where using the SQL Native Client Configuration on the client side. My concern was that it says "all connections from that client will request SSL encryption to any SQL Server to which that client connects". This SSIS client will still need to connect to other SQL Servers locally in my datacenter that don't have a certificate and SSL connections setup on them, is this OK or does it make more sense to NOT use the Native Client Configuration settings and instead use the method described later in that article in the section "Enable encryption for a specific client" where it talks about exporting a copy of the certificate from the SQL Server server's MMC and importing it using the client computer's MMC?
Just looking for opinions from people who've set this up before.
February 22, 2017 at 12:49 pm
I'm required to enable encryption for all my SQL servers, so I've had to mess with this some. We have to set the SQL server to "Enforce Encryption," rather than on the client-side. What I found in preparing to enable this is that .NET native applications will simply work and encrypt, while some of our applications had to have a switch enabled.
(ColdFusion was a bear to figure out)
What I would expect to happen if you enable it on the client side (and per the linked article by NOT enabling the "Enforce Encryption" option on the SQL Server) is that the client would then try to encrypt its' connection to EVERY SQL Server it tries to connect to. Whether it would then fall back to not encrypting if the target didn't support it, I don't know. I would lean towards probably not, as when I would set my SSMS connection to "encrypt" then try to connect to a SQL that I hadn't yet set up to encrypt, the connection would fail.
I guess the question to ask might be, are there other applications connecting to the SQL Server in question, and will they support the connections being encrypted? If so, then I'd say set the enforce option on the server, not the client to avoid possible headaches with the SSIS client needing to talk to non-encrypted connection servers.
February 22, 2017 at 1:50 pm
OK, thanks for your input!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply