- SQLFrontline.exe connects to the SQL Server(s) that you’ve configured to be checked, runs lightweight queries and collects the results.
- The results are encrypted and compressed in memory and uploaded to write-only Azure blob storage.
- The uploaded file is extracted and loaded into a SQL Azure database.
- Diagnostic queries are run over your servers’ data and you get sent an email with the findings and recommendations.
All data is encrypted in transit and at rest.
1. SQLFrontline.exe runs lightweight queries and encrypts the results
By default, SQLFrontline encrypts all connections it makes to SQL Server. It also does not trust self-assigned server certificates by default (which prevents man-in-the-middle attacks).
If you are running against SQL Servers in your domain, and those SQL servers do not have CA trusted certificates installed (most won’t have out of the box), you can elect to trust your local servers’ self-installed certificates.
If you receive an error message similar to this one during data collection:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.
You will need to either install a CA trusted certificate on your server, or add the -T command line parameter to tell SQLFrontline that you want to trust your local server’s certificate.
Once a connection is established, we collect data about your SQL Servers and databases. You can examine all queries that are run, and examine their output by specifying a command line parameter which will save an unencrypted version of the file we upload to your local file system.
We use a best practice encryption technique to encrypt the data in flight.
Data that is collected:
- Server, database, table, index, constraint, stored procedure and certificate names – if you have client-specific information in any of these you should carefully consider whether that’s going to be a problem. Certificate names are collected only to make sure that you are backing up any certificates that you’re using for Transparent Data Encryption (TDE) and Always Encrypted.
Data that is NOT collected:
- Passwords, queries and their result data – your application(s) could execute queries like “SELECT * FROM dbo.SensitiveInfo WHERE SomeSensitiveColumn = ‘ABC 456 789’” – which means exposing those query parameters could potentially be a security risk. In a future release, you will able to opt-in to the collection of queries and execution plans. All data is stored and transmitted encrypted.
2. SQLFrontline.exe sends encrypted data to blob storage
The collector application sends the encrypted file to Azure blob storage, where it remains encrypted. The encrypted file is stored in Azure blob storage in a write-only container.
3. Data is loaded into a SQL Azure database
We unencrypt the uploaded file into a cloud server’s memory, then load it into a SQL Azure database server for analysis.
The database’s files are encrypted at rest using SQL Azure’s Transparent Data Encryption (TDE). Every client’s data is stored inside a single cloud based SQL Azure database. No client has any access to this database.
4. We run diagnostic queries, and send you an email with recommendations.
We will send you a plain English email with recommended changes/actions.
The email we send you is not encrypted. If someone was intercepting internet traffic between our email servers, they could see the contents of the emails. If you want to receive encrypted email, talk to us to discuss an approach.