Data Migration Assistant Tool- Security Considerations

Rahul 251 Reputation points
2024-02-01T14:13:37.15+00:00

Hi, I am performing the Data Migration from one sql server to another sql server using the Data Migration assistant tool. I am migrating highly confidential data , hence I want the data at rest and data at transit should be fully secure at each point. Source Database- Local On premises sql server instance. Target Database-SQL server instance Question1- What is meant by Encrypt connection, what happens when this checkbox is selected ? Question-2 What is meant by trust server certificate and when this checkbox is selected ? Question-3 How the Data migration assistant tool ensures the Data at rest and Data in transit is fully secure ? Question-4 Please share certain diagram which ensure the data at rest and data in transit are fully secure ? User's image

Azure SQL Database
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
507 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 33,341 Reputation points MVP
    2024-02-01T18:34:22.2066667+00:00

    Q1: With "Encrypt connection" you have encryption in-transit. When a client first attempts a connection to SQL Server / SQL Azure, it sends an initial connection request. Consider this a "pre-pre-connection" request. At this point the client does not know if SSL/Encryption is required and waits an answer from SQL Server/SQL Azure to determine if SSL is indeed required throughout the session (not just the login sequence, the entire connection session). A bit is set on the response indicating so. Then the client library disconnects and reconnects armed with this information. When you set "Encrypt connection" setting you avoid the "pre-pre-connection", you are preventing any proxy from turning off the encryption bit on the client side of the proxy, this way attacks like man-in-the-middle attack are avoided.

    Q2: About the "Trust server certificate" uncheck it to force verification of the server certificate use on the in-transit encryption. This is explained here.

    Q3: Configure Transparent Data Encryption on your local database to protect the data at rest on your local server. Azure SQL has TDE enabled by default when an Azure SQL Database is created.

    Q4: On this URL you will find diagram for TDE. On this other article you will find images on how TLS is used for in-transit encryption and more images explaining TDE. Hope this helps.