"user not authorised" - SQL tables

[vanessafvg]

I have posted about this before, but hoping someone can help

have an extremely frustrating problem which I am not able to resolve.

When I do a get data on a SQL server database, as long as I don't specify the database i can pull the data into my Power BI model.  If i do it any other way i get "User not authorised"  

So in other words I can parameterise the server name, but the database I have  to select from the drop down, not type in the database (optional) section.  As long as i do that way i have no issues grabbing data.    So currently i cannot parameterise or write native sql queries. 

I do get a message box popping just after i have selected the database via the drop down box to do with encryption, however it goes away after the first time

this is what i get in the power bi trace file 

AuthorizationError,Exception:Exception:\r\nExceptionType: System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=**********\r\nMessage: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)\r\nStackTrace:\n   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)\r\n   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, ...

[v-caliao-msft]

Hi  @vanessafvg,

I have tested it on my local environment (Version: 2.42.4611.701 64-bit (January 2017)), we cannot reproduce this issue. Which version of Power BI desktop are you using? In your scenario, please ensure that the user that you login into Power BI Desktop have the permisssion to the SQL Server database that you want to connect to.

Besides, please try to use the latest version and check if this issue persists or not. The latest version of Power BI Desktop is available on the link below.
https://powerbi.microsoft.com/en-us/get-started/

Regards,

Charlie Liao

[vanessafvg]

Hi Charlie

I am on the latest version - as stipulated in my post, i can connect as long as i dont put in the database name.  So there is nothing amiss with connectivity to the server and then selecting via the drop down.  

The one thing that i am doing is that i am loggin on to the dev domain when i log into Power BI, but like i say, as long as i only select the server, i can still get a list of all the databases on that server.   It just seems to me that when one specifies the actual database that it all goes wrong.