Azure SQL and MSSQL Server Secure Access Restrictions and Requirements

  • Author: Ronald Fung

  • Creation Date: 24 May, 2023

  • Next Modified Date: 24 May, 2024

A. Introduction

Relational: Client programs that are written in an object-oriented programming (OOP) language often use SQL drivers, which return queried data in a format that is more relational than object oriented. C# using ADO.NET is one example. The OOP-relational format mismatch sometimes makes the OOP code harder to write and understand.

ORM: Other drivers or frameworks return queried data in the OOP format, avoiding the mismatch. These drivers work by expecting that classes have been defined to match the data columns of particular SQL tables. The driver then performs the object-relational mapping (ORM) to return queried data as an instance of a class. Microsoft’s Entity Framework (EF) for C#, and Hibernate for Java, are two examples.

More info about access

B. How is it used at Seagen

If you are connecting through a whitelist IP, you need to update to use a private endpoint

Please see this documentation to apply a private endpoint and DNS to your MSSQL Server

Once you start using a private endpoint and turn off public IP access, this is how to connect to MSSQL server instances in Azure

  • Denied connections will be everyone accessing from a public IP addess not using Seagen VPN

    • Seagen will not allow IP whitelisting for access of MSSQL servers

  • Allowed conections to a MSSQL server in Azure through the FQDN is availible if you are:

    • Using a VM jumpbox that is part of the coprate VNET in Azure (or on VPN)

    • Using a laptop and are connected thorugh Seagen VPN

    • On the corporate network in the office or elsewhere

If you already have secured your database using a private endpoint and have public access disabled

Simply connect to the database from either being on Seagen VPN or being on the company internal network. You will be able to connect to your server through the normal process you currently ues by accessing the FQDN of your server.

C. Features

Both Azure SQL Database and Microsoft SQL Server provide a range of features for secure access to your databases. Here are some of the key features:

  1. Authentication: Both Azure SQL Database and Microsoft SQL Server support various authentication methods such as Windows authentication, SQL Server authentication, and Azure Active Directory authentication.

  2. Encryption: Both Azure SQL Database and Microsoft SQL Server provide encryption features to help secure your data in transit and at rest. This includes support for Transport Layer Security (TLS) encryption for network traffic and Transparent Data Encryption (TDE) for data at rest.

  3. Firewall: Both Azure SQL Database and Microsoft SQL Server provide firewall features that enable you to restrict access to your databases based on IP addresses.

  4. Access control: Both Azure SQL Database and Microsoft SQL Server provide access control features that enable you to control who has access to your databases and what actions they can perform. This includes roles, permissions, and auditing.

  5. Multi-factor authentication: Azure SQL Database provides additional security features such as multi-factor authentication (MFA) to add an extra layer of security when accessing your database.

  6. Threat detection: Azure SQL Database provides built-in threat detection features that can identify potential security threats and anomalous activities, helping you to proactively protect your database.

  7. Always Encrypted: Azure SQL Database provides the Always Encrypted feature that enables you to encrypt sensitive data in your database at the application level, ensuring that the data is always encrypted, even when it is being processed by the database.

Overall, both Azure SQL Database and Microsoft SQL Server provide a range of features for secure access to your databases, including authentication, encryption, firewall, access control, multi-factor authentication, threat detection, and Always Encrypted. By leveraging these features, you can ensure that your databases are secure and protected from unauthorized access and potential security threats.

D. Where Implemented

LeanIX

E. How it is tested

Testing Azure SQL and MSSQL Server Secure Access involves ensuring that the access is secure and meeting the needs of all stakeholders involved in the project. Here are some steps to follow to test Azure SQL and MSSQL Server Secure Access:

  1. Define the scope and requirements: Define the scope of the project and the requirements of all stakeholders involved in the project. This will help ensure that Azure SQL and MSSQL Server Secure Access is designed to meet the needs of all stakeholders.

  2. Develop test cases: Develop test cases that cover all aspects of Azure SQL and MSSQL Server Secure Access functionality, including secure access to the database, encryption of data in transit and at rest, and management of access control. The test cases should be designed to meet the needs of the organization, including scalability and resilience.

  3. Conduct unit testing: Test the individual components of Azure SQL and MSSQL Server Secure Access to ensure that they are functioning correctly. This may involve using tools like PowerShell or Azure CLI for automated testing.

  4. Conduct integration testing: Test Azure SQL and MSSQL Server Secure Access in an integrated environment to ensure that it works correctly with other systems and applications. This may involve testing Azure SQL and MSSQL Server Secure Access with different operating systems, browsers, and devices.

  5. Conduct user acceptance testing: Test Azure SQL and MSSQL Server Secure Access with end-users to ensure that it meets their needs and is easy to use. This may involve conducting surveys, interviews, or focus groups to gather feedback from users.

  6. Automate testing: Automate testing of Azure SQL and MSSQL Server Secure Access to ensure that it is functioning correctly and meeting the needs of all stakeholders. This may involve using tools like Azure DevOps Pipelines to set up automated testing pipelines.

  7. Monitor performance: Monitor the performance of Azure SQL and MSSQL Server Secure Access in production to ensure that it is meeting the needs of all stakeholders. This may involve setting up monitoring tools, such as Azure Monitor, to track usage and identify performance issues.

  8. Address issues: Address any issues that are identified during testing and make necessary changes to ensure that Azure SQL and MSSQL Server Secure Access is functioning correctly and meeting the needs of all stakeholders.

By following these steps, you can ensure that Azure SQL and MSSQL Server Secure Access is tested thoroughly and meets the needs of all stakeholders involved in the project. This can help improve the quality of Azure SQL and MSSQL Server Secure Access and ensure that it functions correctly in a production environment.

F. 2023 Roadmap

????

G. 2024 Roadmap

????

H. Known Issues

There are several known issues that can impact Azure SQL and MSSQL Server Secure Access. Here are some of the most common issues to be aware of:

  1. Configuration issues: Configuration issues can arise when configuring Azure SQL and MSSQL Server Secure Access, leading to issues with access control and encryption. It is important to ensure that the service is properly configured to avoid these issues.

  2. Authentication issues: Authentication issues can arise when accessing Azure SQL and MSSQL Server Secure Access, leading to issues with authentication and authorization. It is important to ensure that authentication is properly configured to avoid these issues.

  3. Compatibility issues: Azure SQL and MSSQL Server Secure Access may not be compatible with all platforms, devices, or languages. It is important to ensure that Azure SQL and MSSQL Server Secure Access is compatible with the organization’s existing infrastructure before implementation.

  4. Security issues: Security is a critical concern when it comes to Azure SQL and MSSQL Server Secure Access. It is important to ensure that Azure SQL and MSSQL Server Secure Access is secured and that access to the solution is restricted to authorized personnel.

  5. Deployment issues: Deployment issues can arise when deploying Azure SQL and MSSQL Server Secure Access in a complex environment. It is important to ensure that the deployment process is thoroughly tested and that all dependencies are accounted for.

  6. Performance issues: Performance issues can arise when Azure SQL and MSSQL Server Secure Access is under heavy load, leading to issues with network traffic and latency. It is important to monitor performance and address any issues that arise.

Overall, Azure SQL and MSSQL Server Secure Access requires careful planning and management to ensure that it is functioning correctly and meeting the needs of all stakeholders involved in the project. By being aware of these known issues and taking steps to address them, you can improve the quality of Azure SQL and MSSQL Server Secure Access and ensure the success of your project.

[x] Reviewed by Enterprise Architecture

[x] Reviewed by Application Development

[x] Reviewed by Data Architecture