question

HarigGeorgeWilliam-0387 avatar image
0 Votes"
HarigGeorgeWilliam-0387 asked AmeliaGu-msft answered

SQL Server Sharred Memory and Encryption

Hi All,

So, My burning question that I want to find an answer to is: Are Shared Memory connections on a local system to Sql Server encrypted if you configure the “Force Encryption” to be “On” and “Extended protection” is set to “Allowed or Required”?

I can see that a TCP connection would be encrypted, but why would a shared memory connection be encrypted?

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi HarigGeorgeWilliam-0387,

Welcome to Microsoft Q&A.

Are Shared Memory connections on a local system to Sql Server encrypted if you configure the “Force Encryption” to be “On” and “Extended protection” is set to “Allowed or Required”?

Yes. You can use the following query to check if the shared memory connection is encrypted when configuring the “Force Encryption” to be “On” and “Extended protection” is set to “Allowed or Required”:

 select session_id, net_transport, encrypt_option from sys.dm_exec_connections

115189-image.png

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (14.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SimpleSamples avatar image
0 Votes"
SimpleSamples answered SimpleSamples commented

To answer the question why would a shared memory connection be encrypted theoretically, if that is done then it would be to thwart anyone from getting the data that can hack into shared memory that should not have access to the data. Shared memory must use virtual storage that uses an external drive. Unencrypted data in an external drive could theoretically be accessed in an unauthorized manner.

There are many relevant articles if we want to search for them. For example Encrypt Your Windows Pagefile To Improve Security - gHacks Tech News explains how to encrypt the pagefile and how that is relevant. And encryption - Why encrypt data in memory? - Information Security Stack Exchange is relevant to shared memory too.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi and Thank you for your answer, unfortunately your answer has not answered the question that I asked. May be I should go it to a bit more detail. So we already know shared memory connection are attempted first, then named pipes then Tcp/Ip. And we already know that a shared memory connection is for a local connection on the same computer.

So we also know that the server can be told to force connections to be encrypted by setting “Force Encryption” to be “On” and “Extended protection” is set to “Allowed or Required” in the Sql Server Configuration Settings. And this is for sure for Tcp/Ip connections and that is great and wonderful. So does setting those also encrypt Shared Mmemory Connections???. We also know that Encrypted connections require more resources and take longer to process. So what I want to know, are shared memory connections (a connection that is local on the server) encrypted when the server is set to force encryptions on?

As an example, IIS web server on the same server as sql server, connections from asp.net app on local machine will use shared memory (unless I disable it), so there is in my mind no reason to encrypt the connection between asp.net app and database. especially if the IIS app is already serving data using https (ssl/tls). So the data entering and leaving the server is encrypted. So why encrypt / decrypt data going to the asp.net app and then again encrypt and decrypt the data going the app to the database. So I want tcp/ip connections encrypted but i do not want SM encrypted. I'm trying to save on processing power.

1 Vote 1 ·
SimpleSamples avatar image SimpleSamples HarigGeorgeWilliam-0387 ·

you should provide all relevant details in the original question.

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi HarigGeorgeWilliam-0387,

So I want tcp/ip connections encrypted but i do not want SM encrypted.

You can create a connection string in the format tcp:<servername>[\<instancename>],<port> or tcp:<IPAddress>[\<instancename>],<port> to make sure the connection is using TCP/IP.

Best Regards,
Amelia



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.