question

GunisettiSuneethaSGRESESWSINSCA-8135 avatar image
0 Votes"
GunisettiSuneethaSGRESESWSINSCA-8135 asked LimitlessTechnology-2700 answered

Encryption in linked servers with Access database

In our product, we have are using linked server with Access database, which is located in same server. (SQL Server + MDB files are in same server).

During security vulnerability scans at organization, the issue raised on linked server with Access database. Issue is 'linked server not using encryption in data transit'.

Use case: Few of the SQL stored procedures are querying data from linked server which intern use link enabled to Access database.

We tried adding Encrypt=yes as connecting string while adding linked server definition, with this option, linked server throwing error as "could not initialize OLEDB provider for the linked server 'database'.

Looking for help in this use case as we dont see any article on encryption used with linked server on access databases.

sql-server-generalwindows-serveroffice-access-dev
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.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 commented

We tried adding Encrypt=yes

That works if the linked server points to an other SQL Server, which supports encrypted communication.
MS Access don't support encryption.
https://www.connectionstrings.com/access/

· 3
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.

Thank you for the reply. If not Access, does linked server will have its own encryption that can be applied to any database linked?

0 Votes 0 ·
TomPhillips-1744 avatar image TomPhillips-1744 GunisettiSuneethaSGRESESWSINSCA-8135 ·

A linked server to an Access database uses the Access driver. MS Access does not support encrypting the connection.

Your best method is to migrate your Access database to SQL Server.

0 Votes 0 ·
OlafHelper-2800 avatar image OlafHelper-2800 GunisettiSuneethaSGRESESWSINSCA-8135 ·

does linked server will have its own encryption

No, it don't, a linked server utilize the data provider for the data source, here JET or ACE and if the data provider don't support (as here) then you can't do anything.
0 Votes 0 ·
LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered

Hi there,

This is a general error message, could you please share us more error message about this issue from SQL server error log. For SQL Server Authentication, a login with the exact same name and password must exist on the remote server.

The below thread discusses the same issue and you can try out some troubleshooting steps from this and see if that helps you to sort the Issue.

https://docs.microsoft.com/en-us/answers/questions/105916/cannot-initialize-the-data-source-object-of-ole-db.html

https://docs.microsoft.com/en-us/answers/questions/196124/sql-server-cannot-initialize-the-data-source-objec.html

Enable encrypted connections to the Database Engine https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15



--If the reply is helpful, please Upvote and Accept it as an answer--

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.