QlikSense OLEDB Connection via Windows Azure

[patbuchanan]

I'm trying to use Qlik Sense but I'm running into an issue connecting to my OLEDB Windows Azure Databases that I use with Qlikview. When I got through a similar wizard in Qlikview I can copy and paste my Database name in and my connection works but with Qlik Sense it is a dropdown box and it won't let me copy and paste my Database name.  In order to click the drop down box your username must have select rights to the master database and Windows Azure logins don't have access to the master database.

[Ian_Crosland]

Hi,

I encountered the same issue and it turns out the IP address was not setup in a firewall rule on Azure, try testing the connection in SQL Server management studio and you will get an error containing a firewall message

this post describes the issue:

Create and connect to a SQL Database on Windows Azure | SQL Server DBA Diaries

thanks

Ian

[arixooo123]

Hi Ian,

I have the same issue and can't connect to our DW on Azure through Qlik sense. Would you please check below link and put your comment?

Reading data from Azure SQL server in Qlik sense

[Ian_Crosland]

‌Hi

You will need to use the OLEDB driver for SQL Server not the Directory Services driver in your post

Thanks

[arixooo123]

Thanks Ian,

I tried that too, with OLEDB driver I can't even connect to the server. When I test the connection it fails. But with Directory Services I could connect (as you can see in the snapshot) but can't see the database or select any table

[Ian_Crosland]

‌Did you check the IP address was setup in the firewall settings, previous post gives a few more details

[arixooo123]

Thanks,

I actually connect to the server every day (Through SSMS), it's OK. I guess it means that the IP address is already configured. But the point is in SSMS I must choose Active Directory Universal as my Authentication. Do you think that's important?

[patbuchanan · Accepted solution]

Also, what solved this for me was giving my user read access to the master database, so the login could read the dropdown box of databases since QlikSense doesn't allow you to copy/paste the name of the database

[juraj_misina]

Just adding what worked for me. Since I did not obtain the read access to master database, what I did is I created a dummy OLE DB connection (really, whatever OLEDB connection), just to have a connection pop up in QMC. Then I edited the connection string in QMC and added the "Initial catalog" information. Works like charm.

Thanks to patbuchanan‌ for pointing this out, it helped me find a workaround.