Month: February 2017

Managing firewall rules for Azure databases with SSMS

When you create a new Azure database, you usually need to open the firewall to remotely administrate or query this database with SSMS. An option is to create rules from the Azure Portal. It’s surely a convenient way to do it when you create a database but I prefer to keep a minimum of tools and when the Azure portal is not open, I prefer to not have to open it just to define a few firewall rules.

Opening the firewall with SSMS is a kind of chicken and eggs problem: to connect to your database/server, you need to open the firewall. Hopefully, SSMS has a great suite of screens to call the underlying API of Azure Portal and open the firewall for the computer running SSMS.

Attempt to connect to your Azure database.

ssms-azure-01

If the firewall is not already open for this IP, you’ll receive the following screen:

ssms-azure-02

The first step is to identify you with your Azure subscription.

ssms-azure-03If you’ve created your account a long time ago, you could receive this screen … If your account is linked to professional resources (granted by your comany) the good choice is probably the first. If you’re developing from home with resources that you’ll pay by yourself, the correct choice is probably the second.

ssms-azure-05

When you’re successfully identified, you can create a new rule. Open the firewall for your own IP or for a subnet.

ssms-azure-06

Now, you can connect and query your database or the virtual master database (open the folder System Databases).

ssms-azure-07

Firewall rules applicable at the whole server are effectively stored in the master database, you can list them by querying the view sys.firewall_rules.

ssms-azure-08

At this moment, you see that I was a bit lazy and didn’t clean all these rules since a long time. If you want to remove some rules, you can use the stored procedure sp_delete_firewall_rule.

ssms-azure-10

It’ll clean-up your list of firewal rules.ssms-azure-11

 

If you want you can create firewall rules at the database level by connecting to the database and using the sys.database_firewall_rules. The firewall rules at the database level are evaluated before these at the server level. To improve performance, server-level firewall rules are temporarily cached at the database level. To refresh the cache, see DBCC FLUSHAUTHCACHE

Web.Contents and 404 page not found in Power BI

The M language (empowering Power Query and Power BI) supports the try ... otherwise ... feature. It is handy to catch errors when processing some of the rows and handle these cases in alternative paths.

Unfortunately this feature is not intuitive when surrounding a call to the function Web.Contents(). Intuitively you could expect that an answer 404: page not found would start the otheriwse path. But it’s not the case, an error will throw!

To correctly manage this kind of error, you’ll need to add a second parameter to your Web.Contents call (after the url): [ManualStatusHandling={404}]). This parameter specifies to Web.Contents() that you don’t want to apply the default behavior of Power BI when receiving a 404 error: you’ll handle this case by yourself and not stop the load of the table.

The pragmatic option is probably to continue your transformation flow independently of the response received from the web server. Your next step will (probably) miserably fail and you’ll be able to add a try ... otherwise ... on this step to handle an error.

But, if you really want to do this cleanly, you’ll have to parse the metadata of the result of Web.Contents. The function Value.Metadata gives you this opportunity. In the metadata, you’ll find a Response.Status field where you’ll be able to check the value received: 404, 100, … Based on this value you can decide which path to follow with a simple “ìf … then … else“`

let
  Source = Web.Content(
    "http://unexisting.com"
    , [ManualStatusHandling={404}])
  ),
  ResponseCode = Value.Metadata(Source)[Response.Status],
  NextStep = if ResponseCode=404 then ... else ...

How to connect to PostgreSQL with NBi?

I often receive questions such as “Does NBi work with PostgreSQL?” or “Is it possible to use another database than SQL Server?”. The answer is definitively: YES! NBi is working with all databases supporting OleDB or ODBC connections.

Yesterday, I edited a blog post for the connection to MySQL, today we’ll see how to connect to PostgreSQL from NBi by using both the ODBC and the OleDB connectors. At the opposite of MySQL, PostgreSQL has at least one ODBC driver and at least one OleDB provider still maintained (and even actively developed).

I’ll start by the ODBC driver for PostgreSQL. You can find the binaries on the official PostgreSQL website. Download them and install this driver. You can check that the ODBC driver has been correctly installed in the “Program and Features” panel:

postgresql-odbc-connector

 

Another great way to check that the ODBC drivers are installed is to user PowerShell and the cmdlet

Get-OdbcDriver

This method returns the following list where you should find something related to PostgreSQL.

postgresql-odbc-driver

This method will also give you the exact name of the driver, something where my google-fu usually miserably fails. In this case I’ve two drivers named PostgreSQL ANSI(x64) and PostgreSQL Unicode(x64). Be sure to use these exact names in your connection strings!

When these connectors are successfully installed, edit your test-suite and define the connection string to your PostgreSQL server as:

<query
   connectionString=
      "Driver={PostgreSQL ANSI(x64)};
       Server=127.17.0.2;
       Database=postgres;
       UId=CI-Build;
       Pwd=Xyz;" 
>
  <![CDATA[
     select 'Hellow world'
  ]]>
</query>

That’s it!

For the OleDB provider, I’ll use the commercial version provided by Intellisoft and available on this page. This product comes with a free trial. Once downloaded and installed, you can check that it has correctly been registered by running the following PowerShell cmdlet:

(New-Object system.data.oledb.oledbenumerator).GetElements()

and it should contain the following result:

postgresql-oledb-provider

Unfortunately, this method doesn’t return the nickname of the provider that we must use in our connection string. In this specific case, this name is PNGP.1.

Because this OleDB provider is not pre-registered by NBi, we need to map it to the OleDb namespace as explained in the documentation of NBi. The config file must be updated with:

<nbi testSuite="PostgreSQL-TestSuite.nbits">
  <providers>
    <add id="PGNP.1" invariant-name="System.Data.OleDb"/>
  </providers>
</nbi>

Once it’s done, you can safely edit your connection string into your test-suite.

<query
  connectionString=
    "Provider=PGNP.1;
     Data Source=127.17.0.2;
     Initial Catalog=postgres;
     User ID=CI-Build;
     Password=Xyz;"
>
  <![CDATA[
     select 'Hello World'
  ]]>
</query>

We could move this connection string to the settings (references or defaults) or to the config file.

You can now configure a connection to a PostgreSQL instance without any problem!

How to connect to MySQL with NBi?

I often receive questions such as “Does NBi work with MySQL/PostgreSQL?” or “Is it possible to use another database than SQL Server?”. The answer is definitively: YES! NBi is working with all databases supporting OleDB or ODBC connections.

Today, I’ll specifically answer the question about MySQL, tomorrow I’ll edit another blog post about PostgreSQL. For MySQL, I’ll connect from NBi by using an ODBC connector. Indeed, MySQL has no support for an OleDB connector. To be 100% transparent, some old versions of OleDB connectors exist but it doesn’t sound to have any active development on these projects.

Start by downloading the latest version of the ODBC driver for MySQL from the MySQL website. Then install it. You can check that the ODBC driver has been correctly installed in the “Program and Features” panel:

mysql-odbc-connector

Another great way to check that the ODBC drivers are installed is to user PowerShell and the cmdlet

Get-OdbcDriver

This method returns the following list where you should find something related to MySQL.

mysql-odbc-driver

Do you need the 32-bits(x86) or 64-bits(x64) drivers? It depends of the application using the ODBC driver … both need to match. When using the nunit-console or the GUI of NUnit, you’ll need the 64-bits version. But if you’re running your tests from Visual Studio then you’ll need the 32-bits version. You can install both on the same computer but it’s apparently recommended to install first the 64-bits version and then the 32-bits. Haven’t checked this personally.

When the connector is successfully installed, edit your test-suite and define the connection string to your MySQL server as:

<query
   connectionString=
      "Driver={MySQL ODBC 5.3 UNICODE Driver};
       Server=127.17.0.3;
       Database=adventureworks;
       User=CI-Build;
       Password=Xyz;" 
>
  <![CDATA[
     select count(*) from adventureworks.address
   ]]>
</query>

We could also use this connection string in our settings (defaults or references) or in our config file.

Now, you can use your MySQL instance exactly the same way you’d use a SQL Server instance.