Thursday, June 7, 2018

Azure SQL Database: Use SQL Server Management Studio to connect and query data

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database for Microsoft Windows. This quickstart demonstrates how to use SSMS to connect to an Azure SQL database, and then use Transact-SQL statements to query, insert, update, and delete data in the database.

Prerequisites

This quickstart uses as its starting point the resources created in one of these quickstarts:

An Azure SQL database. You can use one of these techniques to create a database:

Install the latest SSMS

Before you start, make sure you have installed the newest version of SSMS.

SQL server connection information

Get the connection information needed to connect to the Azure SQL database. You will need the fully qualified server name, database name, and login information in the next procedures.

Log in to the Azure portal.
Select SQL Databases from the left-hand menu, and click your database on the SQL databases page.
On the Overview page for your database, review the fully qualified server name as shown in the following image. You can hover over the server name to bring up the Click to copy option.
If you forget your server login information, navigate to the SQL Database server page to view the server admin name. If necessary, reset the password.

Connect to your database

Use SQL Server Management Studio to establish a connection to your Azure SQL Database server.

Important

An Azure SQL Database logical server listens on port 1433. If you are attempting to connect to an Azure SQL Database logical server from within a corporate firewall, this port must be open in the corporate firewall for you to successfully connect.

Open SQL Server Management Studio.

In the Connect to Server dialog box, enter the following information:

Setting	Suggested value	Description
Server type	Database engine	This value is required.
Server name	The fully qualified server name	The name should be something like this: mynewserver20170313.database.windows.net.
Authentication	SQL Server Authentication	SQL Authentication is the only authentication type that we have configured in this tutorial.
Login	The server admin account	This is the account that you specified when you created the server.
Password	The password for your server admin account	This is the password that you specified when you created the server.

Click Options in the Connect to server dialog box. In the Connect to database section, enter mySampleDatabase to connect to this database.
Click Connect. The Object Explorer window opens in SSMS.
In Object Explorer, expand Databases and then expand mySampleDatabase to view the objects in the sample database.

Query data

Use the following code to query for the top 20 products by category using the SELECT Transact-SQL statement.

In Object Explorer, right-click mySampleDatabase and click New Query. A blank query window opens that is connected to your database.

In the query window, enter the following query:

SQL

SELECT pc.Name as CategoryName, p.name as ProductName
FROM [SalesLT].[ProductCategory] pc
JOIN [SalesLT].[Product] p
ON pc.productcategoryid = p.productcategoryid;

On the toolbar, click Execute to retrieve data from the Product and ProductCategory tables.

Insert data

Use the following code to insert a new product into the SalesLT.Product table using the INSERT Transact-SQL statement.

In the query window, replace the previous query with the following query:

SQL

INSERT INTO [SalesLT].[Product]
        ( [Name]
        , [ProductNumber]
        , [Color]
        , [ProductCategoryID]
        , [StandardCost]
        , [ListPrice]
        , [SellStartDate]
        )
  VALUES
        ('myNewProduct'
        ,123456789
        ,'NewColor'
        ,1
        ,100
        ,100
        ,GETDATE() );

On the toolbar, click Execute to insert a new row in the Product table.

Update data

Use the following code to update the new product that you previously added using the UPDATE Transact-SQL statement.

In the query window, replace the previous query with the following query:

SQL

UPDATE [SalesLT].[Product]
SET [ListPrice] = 125
WHERE Name = 'myNewProduct';

On the toolbar, click Execute to update the specified row in the Product table.

Delete data

Use the following code to delete the new product that you previously added using the DELETE Transact-SQL statement.

In the query window, replace the previous query with the following query:

SQL
```
DELETE FROM [SalesLT].[Product]
WHERE Name = 'myNewProduct';
```
On the toolbar, click Execute to delete the specified row in the Product table.

Actual Post: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-ssms

Connect to SQL Data base with Visual Studio and SSDT

Use Visual Studio to query Azure SQL Data Warehouse in just a few minutes. This method uses the SQL Server Data Tools (SSDT) extension in Visual Studio.

Prerequisites

To use this tutorial, you need:

An existing SQL data warehouse. To create one, see Create a SQL Data Warehouse.
SSDT for Visual Studio. If you have Visual Studio, you probably already have this. For installation instructions and options, see Installing Visual Studio and SSDT.
The fully qualified SQL server name. To find this, see Connect to SQL Data Warehouse.

1. Connect to your SQL Data Warehouse

Open Visual Studio 2013 or 2015.
Open SQL Server Object Explorer. To do this, select View > SQL Server Object Explorer.
Click the Add SQL Server icon.
Fill in the fields in the Connect to Server window.
- Server name. Enter the server name previously identified.
- Authentication. Select SQL Server Authentication or Active Directory Integrated Authentication.
- User Name and Password. Enter user name and password if SQL Server Authentication was selected above.
- Click Connect.
To explore, expand your Azure SQL server. You can view the databases associated with the server. Expand AdventureWorksDW to see the tables in your sample database.

2. Run a sample query

Now that a connection has been established to your database, let's write a query.

Right-click your database in SQL Server Object Explorer.
Select New Query. A new query window opens.
Copy this TSQL query into the query window:

SQL
```
SELECT COUNT(*) FROM dbo.FactInternetSales;
```
Run the query. To do this, click the green arrow or use the following shortcut: CTRL+SHIFT+E.
Look at the query results. In this example, the FactInternetSales table has 60398 rows.

Actual Post: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-query-visual-studio

Azure database level firewall queries

sp_set_database_firewall_rule (Azure SQL Database)

Creates or updates the database-level firewall rules for your Azure SQL Database. Database firewall rules can be configured for the master database, and for user databases on SQL Database. Database firewall rules are particularly useful when using contained database users. For more information, see Contained Database Users - Making Your Database Portable.

Syntax


sp_set_database_firewall_rule [@name = ] [N]'name'  
, [@start_ip_address =] 'start_ip_address'  
, [@end_ip_address =] 'end_ip_address'
[ ; ]

Arguments

[@name = ] [N]'name'
The name used to describe and distinguish the database-level firewall setting. name is nvarchar(128) with no default value. The Unicode identifier N is optional for SQL Database.

[@start_ip_address =] 'start_ip_address'
The lowest IP address in the range of the database-level firewall setting. IP addresses equal to or greater than this can attempt to connect to the SQL Database instance. The lowest possible IP address is 0.0.0.0. start_ip_address is varchar(50) with no default value.

[@end_ip_address =] 'end_ip_address'
The highest IP address in the range of the database-level firewall setting. IP addresses equal to or less than this can attempt to connect to the SQL Database instance. The highest possible IP address is 255.255.255.255. end_ip_address is varchar(50) with no default value.

The following table demonstrates the supported arguments and options in SQL Database.

Note

Azure connection attempts are allowed when both this field and the start_ip_address field equals 0.0.0.0.

Remarks

The names of database-level firewall settings for a database must be unique. If the name of the database-level firewall setting provided for the stored procedure already exists in the database-level firewall settings table, the starting and ending IP addresses will be updated. Otherwise, a new database-level firewall setting will be created.

When you add a database-level firewall setting where the beginning and ending IP addresses are equal to 0.0.0.0, you enable access to your database in the SQL Database server from any Azure resource. Provide a value to the name parameter that will help you remember what the firewall setting is for.

Permissions

Requires CONTROL permission on the database.

Examples

The following code creates a database-level firewall setting called Allow Azure that enables access to your database from Azure.

-- Enable Azure connections.  
EXECUTE sp_set_database_firewall_rule N'Allow Azure', '0.0.0.0', '0.0.0.0';

The following code creates a database-level firewall setting called Example DB Setting 1 for only the IP address 0.0.0.4. Then, the sp_set_database firewall_rule stored procedure is called again to update the end IP address to 0.0.0.6, in that firewall setting. This creates a range which allows IP addresses 0.0.0.4, 0.0.0.5, and 0.0.0.6 to access the database.

-- Create database-level firewall setting for only 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';

AZURE SQL Database level firewall rules

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

Overview

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:
Diagram describing firewall configuration.

Diagram describing firewall configuration.

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

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

To set a server-level rule from server overview page, click Firewall in the left-hand menu under Settings:
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