February 22, 2017

Power BI Desktop - Accessing SQL Server Database - Error: The user was not authorized

In this post, I am sharing a solution for a problem I faced while I started using Power BI. When you try to connect with SQL Server as Data Source, you may encounter this error message:

The user was not authorized

And it will not let you proceed.

There could be two possibilities for this problem you can try and fix it. First of all, of-course check if the user credentials are correct for login purpose. In my case, I am using a user name which letting me login to my SQL Server successfully, but when I try to connect through Power BI by same user name, it starts giving me the above error message.

Here is how I fixed it.

Solution 1:

  • Go to SQLSERVERNAME > Security > Logins > select you desired user name

  • Right click on selected user, select Properties, and go to Server Roles page.

  • Assign sysadmin role by setting the checkbox as true.

  • Click OK.

Now if you try login from Power BI desktop client, it should login successfully.

Just in cause, if it still not logged-in, you may try this second option. I don't know the real reason for this behavior but I found on internet while searching for my problem and find this workaround, so I am sharing it here if it may help someone facing similar problem.

Solution 2:

Your log-in screen may be similar to this:

Note that here Windows tab is selected and providing two options as Use my current credentials and Use alternate credentials. You might have tried both and it did not worked.

The trick is that, just use the second tab below Windows, named Database. Similar to this:

Put your user name and password in this tab, and it should work now.

I hope you find this post helpful. I would be happy to receive your thoughts about this, if you have encountered similar problem and fixed it. In case if you have tried some other option, please just mention in the comments below and I will add in this post so others people could get benefit.

February 9, 2017

Cannot open backup device. Operating System error 5

I created small utility program for different routine tasks to run on different schedules, one of which is to take database backup to a local folder, and it was working fine. To have access it across network for selected users, and making it available for copy/move to other backup devices, we shared this folder on network. This was the moment I start getting this error in utility error log. In this post I will show the trick I used to solve this problem in our case. First let me share the script I used to take backups, here it is:

BACKUP DATABASE MyDatabaseName
TO DISK = 'D:\DBBackups\mydb-datetimestamp.Bak'  
 

After sharing the folder on network for specific users, it started generating the following error message:

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device 'D:\DBBackups\mydb-datetimestamp.Bak'. Operating system error 5(Access is denied.).

    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

Solution

  • Open Windows Services Screen. (Start > Administration > Services)
  • Find the Service in the list with name SQL Server (MSSQLSERVER), check for the Log On As column (if it doesn't already exist in the list, you can add it from the View menu > Add / Remove Columns...).

    The name of the account you see in Log on As column, is the account you need to give permissions to the directory where you want to place backups.

  • Right click on the folder. Go to > Properties > Share and Security tabs. And add permissions for required account.
  • Click Apply / OK, and let the permissions take effect.

Now try taking new backup, and it should work successfully.