none
"User not authorised" when specifying SQL database :/ RRS feed

  • Question

  • I 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, ...

    


    Monday, February 20, 2017 11:17 AM

Answers

All replies

  • The most likely explanation is that you have two stored credentials -- one for just the server name and one for the server name plus database -- and that one of the two is not working correctly. Go to your data source settings and delete the one for the server name plus database and see if that fixes the problem.

    If it does, then what's happened is probably that the database-specific credential was configured first to use encryption and that the certificate subsequently expired. Then the server-specific credential was configured to not use encryption.

    (It's just a theory... .)

    Tuesday, February 21, 2017 6:19 PM
  • thanks :) unfortunately i only have one stored credential and that is for the one that works.
    Thursday, February 23, 2017 12:30 PM
  • Okay, then I'm baffled. We give the parameters to the driver and it does the rest. The only difference between the two cases should be that in one we specify the Initial Catalog on the connection string and in the other we don't.

    What happens if you disable encryption for this data source in the Data Source settings?

    Thursday, March 2, 2017 5:31 PM
  • unfortunately that didn't work either.  I wonder if it could be because i  logging into another domain?
    Tuesday, March 14, 2017 8:02 AM
  • Hi Vanessa. Were you ever able to resolve this issue?

    Ehren

    Friday, April 7, 2017 9:33 PM
    Owner