Connecting to SQL Data Warehouse

The most frustrating thing with any new system is often just working out how to connect to it. Oddly, you can't use SSMS with SQL Data Warehouse, but it is fine with SSDT, SSIS, Power BI desktop, sqlcmd, BCP, and a range of Microsoft cloud services - there are PowerShell Cmdlets too. Rob Sheldon provides the details.

One of the newer cloud services to come out of the Microsoft camp is SQL Data Warehouse, a distributed database management system built on Azure SQL Database. Microsoft refers to its new service as an elastic data warehouse with enterprise-class features, an industry first that can grow, shrink, and pause in seconds.

I first wrote about the service in my article Azure SQL Data Warehouse, where I introduced you to SQL Data Warehouse and gave you an overview of the architecture and technologies that drive it. In my second article on the topic, Getting started with Azure SQL Data Warehouse, I delved into some of the specifics of implementing a SQL Data Warehouse database. This time around, I focus on connecting to one of those databases from various client tools.

For the examples in this article, I use the same database I created in the second article. Here are some of the details to help you follow along:

  • Database name: sdwdb1
  • Server name: sdwsrv1.database.windows.net
  • Login account: sdwadmin
  • Database type: AdventureWorksDW sample
  • Resource group: sdwgrp1
  • Subscription name: Pay-As-You-Go

The examples in this article demonstrate how to connect to the sdwdb1 database from SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), SQL Server Integration Services (SSIS), sqlcmd, bcp, PowerShell, and Power BI Desktop.

To try out these examples, the database has to be up and running, so be careful not to accumulate unexpected charges. You have to pay to use SQL Data Warehouse unless you’re running the service under Azure’s 30-day free trial or you have extra MSDN credits. Fortunately, you can pause the database when you’re not using it. Just don’t forget. Those subscription compute fees can add up quickly.

Connecting from SQL Server Management Studio

After I created my first SQL Data Warehouse database, I launched the SQL Server 2014 version of SSMS and tried to connect to the server and database, only to discover that the process was not as straightforward as expected. I dug around a bit into the Internet ethers and soon discovered that Microsoft has yet to add support for SSMS.

In part because I had difficulty believing that Microsoft would sidestep SSMS, and also because I already had limited success connecting to SQL Data Warehouse, I pushed forward to see what I could actually do. First, I created a new Database Engine connection through Object Explorer. In the Connect to Server dialog box, I specified the server name, selected SQL Server Authentication, and then provided the login account name and password, as shown in the following figure.

2411-a16b59c8-b1b1-4b59-b3a1-03b632b318d

When I first clicked Connect, I received a network error. So I tried it again, and this time I was able to establish a connection. Since then, I have discovered that I will often receive a network error on my first attempt to connect, but usually the second attempt works and the server is added to Object Explorer, as shown in the following figure.

2411-902fa47b-4f0c-4504-b9b1-94376a5ca43

What you might notice here, however, is that although the sdwdb1 database is listed, none of its tables appear, which defeats much of the advantages of having Object Explorer available to explore objects. Even so, I tried launching a new query from the database, but received the following error:

2411-55ad7f7a-ef72-4e59-b9a4-80aa082f22d

I had no idea what was going on, so I did what came naturally. I clicked OK and continued on to the new query tab and ran the following SELECT statement:

To my surprise, this worked as it should and returned a list of tables from the sdwdb1 database. Now I was able to at least see what was in there. I was also able to use other system views, such as sys.columns, to retrieve additional schema information.

Having achieved some success, I decided to run the following query against one of the user tables, in this case, FactInternetSales:

The query simply groups the data based on the customer key and calculates a total. When I ran the query, it returned the data in relatively quick order, giving me the following results:

Just to be sure, I tested the query in SSDT and compared the results, and they appeared correct, leaving me feeling somewhat confident that I could at least use SSMS to retrieve data from SQL Data Warehouse. Even so, I do not recommend this approach for connecting to the service and will likely stick with SSDT for now, until Microsoft officially supports SSMS. But at least we know it can be done if it for any reason becomes necessary.

Connecting from SQL Server Data Tools

Unlike SSMS, Microsoft does support connecting to SQL Data Warehouse from Visual Studio, via the database engine features in SSDT. When you get into the Visual Studio/SSDT environment, open SQL Server Object Explorer, which is similar to Object Explorer in SSMS. From there, click the Add SQL Server button.

When the Connect dialog box appears, provide the server name, select SQL Server Authentication, and then specify the login name and password, as shown in the following figure.

2411-1b7f888b-ddab-4311-88aa-90f86b7be0b

When I clicked Connect the first time around, I received the same type of network error I received in SSMS, and just like in SSMS, when I tried a second time, I connected right to the server. Perhaps there’s something going on with my system or network. Perhaps the problem is on the Microsoft side. In any case, SQL Server Object Explorer now listed all the tables, as you can see in the following figure.

2411-d085895d-eb8f-465f-83be-d7c3432811d

From the database, I launched a new query without receiving an error. I then ran the following SELECT statement (the same one I ran in SSMS) and received the same results:

For this article, I’m only demonstrating how to connect to SQL Data Warehouse and retrieve data from a database, but know that there is a lot more you can do with SSDT and the other tools.

Connecting from SQL Server Integrations Services

Also from within SSDT, I created an SSIS project to see whether I could connect to SQL Data Warehouse within an SSIS package. The Microsoft documentation suggests that it’s possible, but I could find no specifics about how to go about this.

I first tried to connect to SQL Data Warehouse by creating an ADO.NET connection, but that resulted in an error, so I instead created an OLE DB connection, which worked. I configured the connection as shown in the following figure.

2411-d4e8be97-079f-4816-b112-21a277a06d4

As you can see, most of the connection settings are similar to what we used in the previous examples, except here we must also specify the database.

Next I added a Data Flow component to the control flow and then, within the data flow, added an OLE DB source component. On the source component’s Connecti on Manager page, I specified the newly created OLE DB connection, selected SQL command as the data access mode, and then added the same SELECT statement I used in the previous examples. The following figure shows the Connection Manager page on my system.

2411-30b45947-4123-4354-b339-8b633f05694

To verify that I was on the right track, I clicked the Preview button and got the results shown in the following figure, which were exactly what I was hoping for.

2411-810e8d1b-2ac4-4722-927d-1fba8986e30

I further verified my connection by jumping to the Columns page in the OLE DB source editor, which again indicated that everything was as it should be.

2411-65fd8365-4a78-46fc-9d4d-20d1dd3d895

As a final test, I added a Data Reader destination component (only for testing purposes), and to the data pipeline I added a data viewer, giving me the data flow shown in the following figure.

2411-ccf8ef58-44b2-4e69-8214-264e3b1bcde

When I ran the SSIS package, the data viewer popped open and verified once again that I was able to connect to SQL Data Warehouse and retrieve the correct data, as shown in the following figure.

2411-402ea8e8-744f-4ee6-9aaf-378164b533d

I stopped running the SSIS package from there because I had accomplished what I had set out to do, which was to demonstrate that SSIS can indeed be used to work with SQL Data Warehouse.

Connecting from the sqlcmd command-line utility

Another option for connecting to a SQL Data Warehouse database is to use the command-line utility sqlcmd, which comes with SQL Server. To use the utility, you must first establish your connection by providing the name of the server and database as well as the login name and password, as shown in the following command:

Of course, in place of <password>, I specified the actual password for the login account. I also included the -I argument, which tells the connection to enable quoted identifiers. You must specify this option when using the sqlcmd utility to connect to a SQL Data Warehouse database.

Also note that, when passing in the account login name, I added the at ( @) symbol, following by the first part of the server name ( sdwadmin@sdwsrv1). If you do not follow this format, your connection will fail. Microsoft documentation is not particularly clear on this point, but the error message is.

If you’ve used the the sqlcmd utility before, you know that after you’ve made your connection, the command prompt changes to numbered lines for entering your T-SQL commands. To test my connection, I used our familiar SELECT statement, followed by the GO command:

As expected, the command returned the same results we saw in the previous examples.

When you’re finished using the sqlcmd utility, you can issue a QUIT command to exit the utility and return to the normal command prompt.

Connecting from the bcp command-line utility

We can also use the bcp command-line utility to connect to a SQL Data Warehouse database. However, unlike the sqlcmd utility, we pass in our query and connection information in a single command:

After specifying the SELECT statement (in double quotes), I included the queryout option and specified the TopCustomer.txt file. As a result, the query output will be saved to the file. I then added the -q argument to enable quoted identifiers and the -c argument to perform the operation using a character data type.

Next I specified the S argument and server name, the d argument and database name, the U argument and login name, and the P argument and account password. As with the sqlcmd utility, we must include the at symbol and server name when specifying the login name.

When I ran the bcp command, it returned the expected results and saved them to the text file, demonstrating that we have yet one more option for connecting to a SQL Data Warehouse database and accessing the data.

Connecting from PowerShell

We can also connect to SQL Data Warehouse by using PowerShell. Microsoft provide a set of cmdlets specifically for this purpose, although the focus is more on management than accessing the data within the database.

Before you can use PowerShell to connect to SQL Data Warehouse, you must install the Azure PowerShell Module by running the Microsoft Web Platform Installer. The installer walks you through the process of adding the module to your local system

After you’ve installed the module, you need to run the following command to log into Azure from within PowerShell:

When you run this command, you will be prompted to enter your Azure credentials. After you’ve done so, you will have to select which Azure subscription to use, even if you have only one subscription. If you don’t know the names of the subscriptions available to your Azure account, you can run the following command to retrieve them:

Once you know the subscription name, you can use the Select-AzureRmSubscription cmdlet to specify that subscription:

In this case, I’m connecting to Azure under my Pay-As-You-Go subscription, the same one I use for SQL Data Warehouse.

The Azure PowerShell module includes a number of cmdlets that let you access server and database information. For example, the following command uses the Get -AzureRm SqlDatabase cmdlet to retrieve information about the sdwdb1 database:

Notice that we must specify the name of the resource group, server, and database. However, when we provide the server name, we include only the first part of the name, not the fully qualified name, as we saw in the previous examples.

We can also use PowerShell to make changes to our SQL Data Warehouse database. For example, the following command starts up the database if it is paused:

If we want to instead pause a running database, we can issue a command similar to the following:

As you can see, the key to working with SQL Data Warehouse through PowerShell is to first install the Azure PowerShell Module and then to log into Azure. From there, you can use the Azure cmdlets to perform various tasks, including adding or removing databases.

Connecting from Power BI Desktop

The final tool we’ll look at is Power BI Desktop. We can also connect to SQL Data Warehouse from the Power BI service itself, but to do so requires a Power BI Pro account. With the desktop version, we can connect to SQL Data Warehouse without having to subscribe to Power BI Pro.

That said, let’s get started. After we launch Power BI Desktop, we can use the Get Data feature to navigate to the SQL Data Warehouse option, as shown in the following figure.

2411-abf04177-144b-4686-a38f-017df991068

When we click Connect, we’re presented with the SQL Server Database screen. As you can see in the following figure, we need to provide only the fully qualified server name.

2411-d9fe57cd-3058-497d-adf1-4a2af67c64e

After we click OK, we must then specify the server login information, as shown in the following figure.

2411-f4e31eb0-c252-4381-bfe3-52954f32243

When we click Connect, the Navigator screen appears, where we can select which tables to include from our data source, as the following figure shows. In this case, I chose the tables DimCustomer, DimDate, DimSalesTerritory, and FacetInternetSales.

2411-5a0e1072-19e3-4b1a-83a0-193ef48e25a

To access the data itself, we must then click Load. From there, we can choose to import the data into Power BI or to use DirectQuery to establish a real-time connection to the data source. As the following figure shows, I selected the DirectQuery option.

2411-1e09c06a-4884-4a23-b08f-a938e329721

That’s all it takes to connect to a SQL Data Warehouse database. If you selected the Import option, the data is loaded into your local Power BI store when you click OK. You can then work with the data at any time. The data at this point is static and separate from the original source. If you selected the DirectQuery option, the database must be running and you must be able to connect to SQL Data Warehouse whenever you work with the data.

After setting up the data source, we can then define the relationships between the tables. As is typical for the type of dimensional modeling used in a data warehouse, our fact table points to the three dimensions, forming three many-to-one relationships. The following figure shows the tables after I defined the three relationships based on the applicable key columns.

2411-440e4c3f-e57c-459b-ad97-974296ee6c1

Once we have the data source and relationships set up, we can start playing with our visualizations. For example, I added the following three figures to my report, using a varying mix of the four tables in the data source.

2411-6406b6b2-4671-407a-8a6a-218dc1488b5

Of course, there’s a lot more to Power BI Desktop than what’s show here. For more information about the application, see the Simple-Talk articles Working with SQL Server data in Power BI Desktop and Power Query Formula Language in Power BI Desktop.

Connecting to SQL Data Warehouse

As you can see, you have many options for connecting to a SQL Data Warehouse database, and we’ve only skimmed the surface of what you can do with each of these tools. In addition, your database listing in the Azure portal also provides connection strings that you can use to connect from an application or service via ADO.NET, ODBC, PHP, or JDBC. You can also connect to SQL Data Warehouse from such Azure services as Data Factory, Machine Learning, and Stream Analytics.

Because SQL Data Warehouse is still in preview, it can be a bit tricky at times to find answers if you run into issues when trying to connect to a database. Keep in mind, however, that SQL Data Warehouse is built on Azure SQL Database, so you will sometimes find answers by looking in SQL Database documentation or related resources.

As with any new product or service, you can run into challenges when trying to get started, and often those challenges revolve around being able to establish the necessary connections. This article demonstrated a number of the options you have for getting connected. However, given that SQL Data Warehouse is still in preview, some of these techniques might change, but at least for now you have a foundation for moving forward regardless of the tools you use to connect to your SQL Data Warehouse databases.

  • 9963 views

  • Rate
    [Total: 14    Average: 4.9/5]
  • fregate

    Connecting to SQL Data Warehouse
    Robert,
    I managed to CREATE SQL Data Warehouse from T-SQL script and access it through SSMS. But I provided credential to Connect to Azure DB Server first.