Resolving errors connecting PowerBI to Linux version of MS SQL Server

John MacKintosh

18 hours ago

[This article was first published on Data By John, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Situation: Cannot connect to SQL Server, running on Linux via WSL, from PowerBI, on MS Windows 10.
I’m receiving an error message similar to below:

DataSource.Error: Microsoft SQL: The target principal name is incorrect.  Cannot generate SSPI context.  
Details:  
    DataSourceKind=SQL  

    Message=The target principal name is incorrect.  Cannot generate SSPI context.
    ErrorCode=-2146232060
    Number=0
    Class=11

Background: I want to connect to my local SQL Server, which is the Linux 2019 version running via Windows Subsystem for Linux, for a particular database, using the SA, rather than Windows login.
I can login to another database on the same server with my Windows credentials

Assessment: Because I have previously connected to the same server in PowerBI using Windows credentials, my attempts to connect with the sa login and password are not working, as the original connection seems to take priority.

Resolution: In PowerBI, File, Options and Settings, Data Source Settings, check each entry for the local server, clear all existing SA logins, amend the one that was using Windows to use SA, then refresh.
I can now see both databases within PowerBI

To leave a comment for the author, please follow the link and comment on their blog: Data By John.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Related