AZURE SQL Database level firewall rules

Azure SQL Database server-level and database-level firewall rules 

Overview

1. Creating and managing firewall rules
2. Manage firewall rules using the Azure portal
3. Manage firewall rules using Transact-SQL
4. Manage firewall rules using Azure PowerShell
5. Manage firewall rules using Azure CLI
6. Manage firewall rules using REST API
7. Server-level firewall rule versus a database-level firewall rule
8. Troubleshooting the database firewall
9. Next steps

Microsoft Azure SQL Database provides a relational database service for Azure and other Internet-based applications. To help protect your data, firewalls prevent all access to your database server until you specify which computers have permission. The firewall grants access to databases based on the originating IP address of each request.

Virtual network rules as alternatives to IP rules

In addition to IP rules, the firewall also manages virtual network rules. Virtual network rules are based on Virtual Network service endpoints. Virtual network rules might be preferable to IP rules in some cases. To learn more, see Virtual Network service endpoints and rules for Azure SQL Database.

Overview

Initially, all Transact-SQL access to your Azure SQL server is blocked by the firewall. To begin using your Azure SQL server, you must specify one or more server-level firewall rules that enable access to your Azure SQL server. Use the firewall rules to specify which IP address ranges from the Internet are allowed, and whether Azure applications can attempt to connect to your Azure SQL server.
To selectively grant access to just one of the databases in your Azure SQL server, you must create a database-level rule for the required database. Specify an IP address range for the database firewall rule that is beyond the IP address range specified in the server-level firewall rule, and ensure that the IP address of the client falls in the range specified in the database-level rule.
Connection attempts from the Internet and Azure must first pass through the firewall before they can reach your Azure SQL server or SQL Database, as shown in the following diagram:

• Server-level firewall rules: These rules enable clients to access your entire Azure SQL server, that is, all the databases within the same logical server. These rules are stored in the master database. Server-level firewall rules can be configured by using the portal or by using Transact-SQL statements. To create server-level firewall rules using the Azure portal or PowerShell, you must be the subscription owner or a subscription contributor. To create a server-level firewall rule using Transact-SQL, you must connect to the SQL Database instance as the server-level principal login or the Azure Active Directory administrator (which means that a server-level firewall rule must first be created by a user with Azure-level permissions).
• Database-level firewall rules: These rules enable clients to access certain (secure) databases within the same logical server. You can create these rules for each database (including the master database) and they are stored in the individual databases. Database-level firewall rules for master and user databases can only be created and managed by using Transact-SQL statements and only after you have configured the first server-level firewall. If you specify an IP address range in the database-level firewall rule that is outside the range specified in the server-level firewall rule, only those clients that have IP addresses in the database-level range can access the database. You can have a maximum of 128 database-level firewall rules for a database. For more information on configuring database-level firewall rules, see the example later in this article and see sp_set_database_firewall_rule (Azure SQL Databases).

Recommendation: Microsoft recommends using database-level firewall rules whenever possible to enhance security and to make your database more portable. Use server-level firewall rules for administrators and when you have many databases that have the same access requirements and you don't want to spend time configuring each database individually.

Important
Windows Azure SQL Database supports a maximum of 128 firewall rules.

Note
For information about portable databases in the context of business continuity, see Authentication requirements for disaster recovery.

Connecting from the Internet

When a computer attempts to connect to your database server from the Internet, the firewall first checks the originating IP address of the request against the database-level firewall rules, for the database that the connection is requesting:

• If the IP address of the request is within one of the ranges specified in the database-level firewall rules, the connection is granted to the SQL Database that contains the rule.
• If the IP address of the request is not within one of the ranges specified in the database-level firewall rule, the server-level firewall rules are checked. If the IP address of the request is within one of the ranges specified in the server-level firewall rules, the connection is granted. Server-level firewall rules apply to all SQL databases on the Azure SQL server.
• If the IP address of the request is not within the ranges specified in any of the database-level or server-level firewall rules, the connection request fails.

Note
To access Azure SQL Database from your local computer, ensure the firewall on your network and local computer allows outgoing communication on TCP port 1433.

Connecting from Azure

To allow applications from Azure to connect to your Azure SQL server, Azure connections must be enabled. When an application from Azure attempts to connect to your database server, the firewall verifies that Azure connections are allowed. A firewall setting with starting and ending address equal to 0.0.0.0 indicates these connections are allowed. If the connection attempt is not allowed, the request does not reach the Azure SQL Database server.

Important
This option configures the firewall to allow all connections from Azure including connections from the subscriptions of other customers. When selecting this option, make sure your login and user permissions limit access to only authorized users.

Creating and managing firewall rules

The first server-level firewall setting can be created using the Azure portal or programmatically using Azure PowerShell, Azure CLI, or the REST API. Subsequent server-level firewall rules can be created and managed using these methods, and through Transact-SQL.

Important
Database-level firewall rules can only be created and managed using Transact-SQL.

To improve performance, server-level firewall rules are temporarily cached at the database level. To refresh the cache, see DBCC FLUSHAUTHCACHE.

Tip
You can use SQL Database Auditing to audit server-level and database-level firewall changes.

Manage firewall rules using the Azure portal

To set a server-level firewall rule in the Azure portal, you can either go to the Overview page for your Azure SQL database or the Overview page for your Azure Database logical server.

Tip
For a tutorial, see Create a DB using the Azure portal.

From database overview page

1. To set a server-level firewall rule from the database overview page, click Set server firewall on the toolbar as shown in the following image: The Firewall settings page for the SQL Database server opens.
   
2. Click Add client IP on the toolbar to add the IP address of the computer you are currently using and then click Save. A server-level firewall rule is created for your current IP address.
   

From server overview page
The overview page for your server opens, showing you the fully qualified server name (such as mynewserver20170403.database.windows.net) and provides options for further configuration.

1. To set a server-level rule from server overview page, click Firewall in the left-hand menu under Settings:
2. Click Add client IP on the toolbar to add the IP address of the computer you are currently using and then click Save. A server-level firewall rule is created for your current IP address.

Manage firewall rules using Transact-SQL

Catalog View or Stored Procedure	Level	Description
sys.firewall_rules	Server	Displays the current server-level firewall rules
sp_set_firewall_rule	Server	Creates or updates server-level firewall rules
sp_delete_firewall_rule	Server	Removes server-level firewall rules
sys.database_firewall_rules	Database	Displays the current database-level firewall rules
sp_set_database_firewall_rule	Database	Creates or updates the database-level firewall rules
sp_delete_database_firewall_rule	Databases	Removes database-level firewall rules

The following examples review the existing rules, enable a range of IP addresses on the server Contoso, and deletes a firewall rule:

SQL

SELECT * FROM sys.firewall_rules ORDER BY name;

Next, add a firewall rule.

SQL

EXECUTE sp_set_firewall_rule @name = N'ContosoFirewallRule',
   @start_ip_address = '192.168.1.1', @end_ip_address = '192.168.1.10'

To delete a server-level firewall rule, execute the sp_delete_firewall_rule stored procedure. The following example deletes the rule named ContosoFirewallRule:

SQL

EXECUTE sp_delete_firewall_rule @name = N'ContosoFirewallRule'

Manage firewall rules using Azure PowerShell

Cmdlet	Level	Description
Get-AzureRmSqlServerFirewallRule	Server	Returns the current server-level firewall rules
New-AzureRmSqlServerFirewallRule	Server	Creates a new server-level firewall rule
Set-AzureRmSqlServerFirewallRule	Server	Updates the properties of an existing server-level firewall rule
Remove-AzureRmSqlServerFirewallRule	Server	Removes server-level firewall rules

The following example sets a server-level firewall rule using PowerShell:

PowerShell

New-AzureRmSqlServerFirewallRule -ResourceGroupName "myResourceGroup" `
    -ServerName $servername `
    -FirewallRuleName "AllowSome" -StartIpAddress "0.0.0.0" -EndIpAddress "0.0.0.0"

Tip
For PowerShell examples in the context of a quick start, see Create DB - PowerShell and Create a single database and configure a firewall rule using PowerShell

Manage firewall rules using Azure CLI

Cmdlet	Level	Description
az sql server firewall-rule create	Server	Creates a server firewall rule
az sql server firewall-rule list	Server	Lists the firewall rules on a server
az sql server firewall-rule show	Server	Shows the detail of a firewall rule
az sql server firewall-rule update	Server	Updates a firewall rule
az sql server firewall-rule delete	Server	Deletes a firewall rule

The following example sets a server-level firewall rule using the Azure CLI:

Azure CLI

az sql server firewall-rule create --resource-group myResourceGroup --server $servername \
    -n AllowYourIp --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

Tip
For an Azure CLI example in the context of a quick start, see Create DDB - Azure CLI and Create a single database and configure a firewall rule using the Azure CLI

Manage firewall rules using REST API

API	Level	Description
List Firewall Rules	Server	Displays the current server-level firewall rules
Create or Update Firewall Rule	Server	Creates or updates server-level firewall rules
Delete Firewall Rule	Server	Removes server-level firewall rules

Server-level firewall rule versus a database-level firewall rule

Q. Should users of one database be fully isolated from another database?
If yes, grant access using database-level firewall rules. This avoids using server-level firewall rules, which permit access through the firewall to all databases, reducing the depth of your defenses.
Q. Do users at the IP address’s need access to all databases?
Use server-level firewall rules to reduce the number of times you must configure firewall rules.
Q. Does the person or team configuring the firewall rules only have access through the Azure portal, PowerShell, or the REST API?
You must use server-level firewall rules. Database-level firewall rules can only be configured using Transact-SQL.
Q. Is the person or team configuring the firewall rules prohibited from having high-level permission at the database level?
Use server-level firewall rules. Configuring database-level firewall rules using Transact-SQL, requires at least CONTROL DATABASE permission at the database level.
Q. Is the person or team configuring or auditing the firewall rules, centrally managing firewall rules for many (perhaps 100s) of databases?
This selection depends upon your needs and environment. Server-level firewall rules might be easier to configure, but scripting can configure rules at the database-level. And even if you use server-level firewall rules, you might need to audit the database-firewall rules, to see if users with CONTROL permission on the database have created database-level firewall rules.
Q. Can I use a mix of both server-level and database-level firewall rules?
Yes. Some users, such as administrators might need server-level firewall rules. Other users, such as users of a database application, might need database-level firewall rules.

Troubleshooting the database firewall

Consider the following points when access to the Microsoft Azure SQL Database service does not behave as you expect:

• Local firewall configuration: Before your computer can access Azure SQL Database, you may need to create a firewall exception on your computer for TCP port 1433. If you are making connections inside the Azure cloud boundary, you may have to open additional ports. For more information, see the SQL Database: Outside vs inside section of Ports beyond 1433 for ADO.NET 4.5 and SQL Database.
• Network address translation (NAT): Due to NAT, the IP address used by your computer to connect to Azure SQL Database may be different than the IP address shown in your computer IP configuration settings. To view the IP address your computer is using to connect to Azure, log in to the portal and navigate to the Configure tab on the server that hosts your database. Under the Allowed IP Addresses section, the Current Client IP Address is displayed. Click Add to the Allowed IP Addresses to allow this computer to access the server.
• Changes to the allow list have not taken effect yet: There may be as much as a five-minute delay for changes to the Azure SQL Database firewall configuration to take effect.
• The login is not authorized or an incorrect password was used: If a login does not have permissions on the Azure SQL Database server or the password used is incorrect, the connection to the Azure SQL Database server is denied. Creating a firewall setting only provides clients with an opportunity to attempt connecting to your server; each client must provide the necessary security credentials. For more information about preparing logins, see Managing Databases, Logins, and Users in Azure SQL Database.
• Dynamic IP address: If you have an Internet connection with dynamic IP addressing and you are having trouble getting through the firewall, you could try one of the following solutions:
  
  • Ask your Internet Service Provider (ISP) for the IP address range assigned to your client computers that access the Azure SQL Database server, and then add the IP address range as a firewall rule.
  • Get static IP addressing instead for your client computers, and then add the IP addresses as firewall rules.

Next steps

• For a quick start on creating a database and a server-level firewall rule, see Create an Azure SQL database.
• For help in connecting to an Azure SQL database from open source or third-party applications, see Client quick-start code samples to SQL Database.
• For information on additional ports that you may need to open, see the SQL Database: Outside vs inside section of Ports beyond 1433 for ADO.NET 4.5 and SQL Database
• For an overview of Azure SQL Database security, see Securing your database

Actual Post: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure

How to add/whitelist IP's to firewall table in Microsoft Dynamics 365 Operations (AX 7)

Below is the query to add the IP addresses to the firewall table(sys.database_firewall_rules) in AX 7.0 to access the database from outside.

Run the below query to see existing IP's
select * from sys.database_firewall_rules;


I executed the below queries to add the IP addresses to the table.

-- Create database-level firewall setting for only for one IP 0.0.0.4 EXECUTE sp_set_database_firewall_rule N'Example DB Setting 1', '0.0.0.4', '0.0.0.4'; -- Update database-level firewall setting to create a range of allowed IP addresses EXECUTE sp_set_database_firewall_rule N'Example DB Setting 1', '0.0.0.4', '0.0.0.6';



Note: My AX is on cloud and we created a custom screen in AX to run the queries.

Below are the links helped me.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-database-firewall-rule-azure-sql-database?view=azuresqldb-current
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure


@Rahul

Unable to recycle AppPool 'AOSService' running Site 'AOSService'. Check your IIS/Azure Environment for correct deployment error in Dynamics AX 365

Severity Code Description Project File Line Suppression State
Error System.InvalidOperationException: Unable to recycle AppPool 'AOSService' running Site 'AOSService'. Check your IIS/Azure Environment for correct deployment. ---> System.Runtime.InteropServices.COMException: The object identifier does not represent a valid object. (Exception from HRESULT: 0x800710D8)
   at Microsoft.Web.Administration.Interop.IAppHostMethodInstance.Execute()
   at Microsoft.Web.Administration.ConfigurationElement.ExecuteMethod(String methodName)
   at Microsoft.Web.Administration.ApplicationPool.Recycle()
   at Microsoft.Dynamics.Framework.Tools.AosAppPoolRecycler.RecycleAppPool()
   --- End of inner exception stack trace ---
   at Microsoft.Dynamics.Framework.Tools.AosAppPoolRecycler.RecycleAppPool()
   at Microsoft.Dynamics.Framework.Tools.BuildTasks.SyncEngineWrapper.Sync(CancellationToken cancellationToken) 0

Got this error while synching the database.

Reason: AOS got stopped in IIS

Solution: Start the AOS

Steps:

Click Start, click Control Panel, and then click Administrative Tools.

Right-click Internet Information Services (IIS) Manager and select Run as administrator.

In the IIS Manager Connections pane, expand the computer name.

Click Application Pools. The Application Pools pane appears in Features View and start the AOS.

Computed Column and Virtual fields in Dynamics 365 for operations

This article provides information about computed and virtual fields, which are the two types of unmapped fields that a data entity can have. The article includes information about the properties of unmapped fields, and examples that show how to create, use, and test them.

Overview

A data entity can have additional unmapped fields beyond those that are directly mapped to fields of the data sources. There are mechanisms for generating values for unmapped fields:

• Custom X++ code
• SQL executed by Microsoft SQL Server

The two types of unmapped fields are computed and virtual. Unmapped fields always support read actions, but the feature specification might not require any development effort to support write actions.

Computed field

• Value is generated by an SQL view computed column.
• During read, data is computed by SQL and is fetched directly from the view.
• For writes, custom X++ code must parse the input value and then write the parsed values to the regular fields of the data entity. The values are stored in the regular fields of the data sources of the entity.
• Computed fields are used mostly for reads.
• If possible, it's a good idea to use computed columns instead of virtual fields, because they are computed at the SQL Server level, whereas, virtual fields are computed row by row in X++.

Virtual field

• Is a non-persisted field.
• Is controlled by custom X++ code.
• Read and write happens through custom X++ code.
• Virtual fields are typically used for intake values that are calculated by using X++ code and can't be replaced by computed columns.

Properties of unmapped fields

Category	Name	Type	Default value	Behavior
Data	IsComputedField	NoYes	Yes	Yes – The field is synchronized as a SQL view computed column. Requires an X++ method to compute the SQL definition string for the column. The virtual column definition is static and is used when the entity is synchronized. After that, the X++ method is not called at run time. No – The field is a true virtual field, where inbound and outbound values are fully controlled through custom code.
Data	ComputedFieldMethod	String		A static DataEntitymethod in X++ to build the SQL expression that will generate the field definition. This property is disabled and irrelevant if the property IsComputedField is set to No. The method is required if the property IsComputedField is set to Yes.
Data	ExtendedDataType	String

Example: Create a computed field

In this example, you add a computed field to the FMCustomerEntity entity. For reads, the field combines the name and address of the customer into a nice format. For writes, your X++ code parses the combined value into its separate name and address values, and then the code updates the regular name and address fields.

1. In Microsoft Visual Studio, right-click your project, and add the existing FMCustomerEntity.
2. In Solution Explorer, right-click the FMCustomerEntity node, and then click Open.
3. In the designer for FMCustomerEntity, right-click the FMCustomerEntity node, and then click New > String Unmapped Field. 
4. Rename the new field NameAndAddress.
5. Update properties of the NameAndAddress unmapped field, as shown in the following screenshot. 
6. Go to FMCustomerEntity > Methods. Right-click the Methods node, and then click New. Ensure that the method name matches the DataEntityView Method property value of the unmapped computed field.
7. Paste the following X++ code into the method. The method returns the combined and formatted NameAndAddress value. Note: The server keyword is necessary.

   private static server str formatNameAndAddress()   // X++
   {
       DataEntityName      dataEntityName= tablestr(FMCustomerEntity);
       List                fieldList = new List(types::String);
       ////Format name and address to look like following
       ////John Smith, 123 Main St, Redmond, WA 98052
       fieldList.addEnd(SysComputedColumn::returnField(DataEntityName, identifierstr(FMCustomer), fieldstr(FMCustomer, FirstName)));
       fieldList.addEnd(SysComputedColumn::returnLiteral(" "));
       fieldList.addEnd(SysComputedColumn::returnField(DataEntityName, identifierstr(FMCustomer), fieldstr(FMCustomer, LastName)));
       fieldList.addEnd(SysComputedColumn::returnLiteral("; "));
       fieldList.addEnd(SysComputedColumn::returnField(DataEntityName, identifierstr(BillingAddress), fieldstr(FMAddressTable, AddressLine1)));
       fieldList.addEnd(SysComputedColumn::returnLiteral(", "));
       fieldList.addEnd(SysComputedColumn::returnField(DataEntityName, identifierstr(BillingAddress), fieldstr(FMAddressTable, City)));
       fieldList.addEnd(SysComputedColumn::returnLiteral(", "));
       fieldList.addEnd(SysComputedColumn::returnField(DataEntityName, identifierstr(BillingAddress), fieldstr(FMAddressTable, State)));
       fieldList.addEnd(SysComputedColumn::returnLiteral(", "));
       fieldList.addEnd(SysComputedColumn::cast(
           SysComputedColumn::returnField(DataEntityName, identifierstr(BillingAddress), fieldstr(FMAddressTable, ZipCode)), "NVARCHAR"));
       return SysComputedColumn::addList(fieldList);
   }
   

   T-SQL for the computed column.

   ( Cast (( ( T1.firstname ) + ( N' ' ) + ( T1.lastname ) + ( N'; ' ) +
               ( T5.addressline1 )
           + ( N', ' ) + ( T5.city ) + ( N', ' ) + ( T5.state ) + (
           N', '
           ) +
               ( Cast(T5.zipcode AS NVARCHAR) ) ) AS NVARCHAR(100))
   )
       AS
   NAMEANDADDRESS
   

   Tip: If you receive error in data entity synchronization because of computed columns, it's easier to come up with the SQL definition in Microsoft SQL Server Management Studio (SSMS) before using it in X++.
8. Rebuild the project.
9. Synchronize the database. Don't forget this step. You can do this by going to Dynamics 365 **> **Synchronize database > Synchronize.

Example: Create a virtual field

In this example, you add a virtual field to the FMCustomerEntity entity. This field displays the full name as a combination of the last name and first name. X++ code generates the combined value.

1. In the designer for the FMCustomerEntity entity, right-click the Fields node, and then click New > String Unmapped Field.
2. In the properties pane for the unmapped field, set the Name property to FullName.
3. Set the Is Computed Field property to No. Notice that you leave the DataEntityView Method empty. 
4. In the FMCustomerEntity designer, right-click the Methods node, and then click Override> postLoad. Your X++ code in this method will generate the values for the virtual field.
5. Paste the following X++ code in for the postLoad override. Notice that the postLoadmethod returns void.

   public void postLoad()
   {
       super();
       //Populate virtual field once entity has been loaded from database
       //Format full name - "Doe, John"
       this.FullName = this.LastName + ", " + this.FirstName;
   }
   

6. Compile your project.

Example: Use a virtual field to receive and parse an inbound field

Imagine that an external system sends the name of a person as a compound value that combines the last and first names in one field that comes into our system. However, our system stores the last and first names separately. For this scenario, you can use the FullName virtual field that you created. In this example, the major addition is an override of the mapEntityToDataSourcemethod.

1. In the designer for the FMCustomerEntity, right-click the Methods node, and then click Override > mapEntityToDataSource.
2. Paste the following X++ code in for the mapEntityToDataSource method.

   public void mapEntityToDataSource(DataEntityRuntimeContext entityCtx, DataEntityDataSourceRuntimeContext dataSourceCtx)
   {
       super(entityCtx, dataSourceCtx);
       //Check if desired data source context is available
       if (dataSourceCtx.name() == "FMCustomer")
       {
           FMCustomer dsCustomer = dataSourceCtx.getBuffer();
           //Find position of "," to parse full name format "Doe, John"
           int commaPosition = strfind(this.FullName, ",",0,strlen(this.FullName));
           //Update FirstName and LastName in the data source buffer to update
           dsCustomer.LastName = substr(this.FullName,0,commaPosition-1);
           dsCustomer.FirstName = substr(this.FullName, commaPosition+1, strlen(this.FullName));
       }
   }
   

   Note: When update is called, mapEntityToDataSource methods are invoked for each data source.

Test the computed and virtual fields

The following main method tests your computed and virtual fields. Both fields are tested in a read action, and the virtual field is tested in an update action.

1. For this example, ensure that you have the data set named Fleet Management (migrated). The data set is available from the dashboard in the browser. Click the menu icon in the upper-right corner, click the APP LINKS menu, and then scroll to find the data set named Fleet Management (migrated).
2. Paste the following X++ code into the startup object of your project. Run your project.

   public static void main(Args _args)   // X++
   {
       FMCustomerEntity customer;
       //Using transactions to avoid committing updates to database
       ttsbegin;
       //SELECT single customer entity record from the database
       select customer
           where customer.Email == "phil.spencer@adatum.com";
       //Read full name (Virtual Field)
       info(customer.FullName);
       //Read formatted NameAndAddress(computed Field)
       info(customer.NameAndAddress);
       //UPDATE full name (virtual field)
       customer.FullName = "Doe, John";
       customer.update();
       //Reselect data from database to get updated information
       select customer
           where customer.Email == "phil.spencer@adatum.com";
       //Read full name (virtual field)
       info(customer.FullName);
       ttsabort;
   }

Refer https://docs.microsoft.com/en-us/dynamics365/unified-operations/fin-and-ops/index for more info.

Share personalizations with other users in Dynamics 365 Operations

Hi All ,

In dynamics 365 operations we have an option to share the user interface personalizations with other users.
Go to personalization of the screen and export it to your local system.

 Then import the file into other users system as shown below.

Please make sure that this will replace the existing personalization.

How to download Dynamics 365 VM Demo

You can access asset library to download VM, just follow this steps

1-go to this link

https://signup.microsoft.com/signup

 then fill information

2- click next to select your country and ...etc 

 3- click create account

you can verify with a call or SMS complete  

4-you are ready to go

5- press ctrl+shfit = N to open the browser in private mode and log in with your account 

https://portal.office.com

6- log in Life Cycle Service 

 https://lcs.dynamics.com

7- accept the agreement and privacy statement

8-click on customer organization to open projects

9-create new project

assign the name and select product name,.etc

10- go to the asset library

11-you can see the below asset types.

click downloadable VHD and click import

you can import files one by one until complete.

12- after complete import, just click on file to download on your local PC

Actual post : http://dynamics365ax2012.blogspot.ae/2018/04/how-to-download-dynamics-365-vm.html