Forum Discussion
Microsoft SQL: The target principal name is incorrect. Cannot generate SSPI context.
Hello all:
I have an Azure SQL server instance up and running-- I can connect just fine to it using SSMS.
When trying to connect via Power BI Desktop to the same database instance, I'm presented with the following:
Details: "Microsoft SQL: The target principal name is incorrect. Cannot generate SSPI context."
My research on this pointed me to a Kerberos authenticaion issue. Do I need to setup a firewall pinhole (rule) to allow Power BI to make a connection to the SQL server instance? What's causing this error to present and how can I fix it? Can someone please guide me here? Thanks.
17 Replies
Hello:
I have an Azure SQL server instance up and running-- I can connect just fine to it using SSMS.
When trying to connect via Power BI Desktop to the same database instance, I'm presented with the following:
Details: "Microsoft SQL: The target principal name is incorrect. Cannot generate SSPI context."
My research on this pointed me to a Kerberos authenticaion issue. Do I need to setup a firewall pinhole (rule) to allow Power BI to make a connection to the SQL server instance? What's causing this error to present and how can I fix it?
Thanks for the guidance.
Hello!
I had the same error when trying to connect to a remote SQL Server.
In my case I believe that the error has been caused by cumulative update KB3189866 for Windows 10. This happened on multiple machines.
Uninstalling the update solved the problem.
I have already reported this as a bug to Microsoft Connect. If you think the problem is due to the same conditions you may want to upvote/comment on my report.
Thanks!
Report to Microsoft Connect:
https://connect.microsoft.com/SQLServer/feedback/details/3103373Question on Microsoft Forum:
https://social.technet.microsoft.com/Forums/office/en-US/f5bb9795-bb29-4191-9c66-2f7e8fffb06d/kb3189866-kills-connections-to-sql-server?forum=win10itpronetworkingFeedback on Feedback Hub:
feedback-hub:?contextid=104&feedbackid=903e3ebc-bccb-4b71-8240-7defd6b03fe5&form=1&src=2
Hi CAKCy,
Thanks for replying. I'm running Windows 7 (which I should have mentioned in my original posting).
So I'm not sure if I'm impacted by that same update you reported in the bug report. I'll check out the links you posted and will also be back on this thread with more information as I uncover it.
CB
Hello again,
Another person who had a problem (though not an SQL issue but a networking one) provided this link with related info. You may want to have a look:
https://technet.microsoft.com/library/security/MS16-114?f=255&MSPPError=-2147217396
Hi hba_powerbi,
Based on your description, you are connecting to Azure SQL rather than SQL Server on Azure VM, right?
If that is the case, what authentication do you use in SSMS and in Power BI Desktop? The above error occurs when you use integrated Security to connect to SQL Server, for more details, please review this KB article.
In your scenario, when connecting to Azure SQL Server from the Power BI Desktop, please ensure firewall rules for the database are configured to "Allow access to Azure services", and you would need to set up a firewall rule to allow Power BI to make a connection. Also make sure you choose Database authentication mode in Power BI Desktop, and enter server admin account and password rather than AD account to connect to the database.
Thanks,
Lydia ZhangHi,
I do have same issue but happening for only one user. Any idea to resolve this issue.
Granted permissions to SQL Server , Power BI gateway and Data Connections and able to see power BI gateway from https://app.powerbi.com/ and data connections looks good but not sure why below error message pops up when trying to connect it from Power BI Desktop.
Unable to connect
We encountered an error while trying to ocnnect.
Details: Microsoft SQL: the target principal name is incorrect.
Cannot generate SSPI context."
I would like to let you know what and all steps i tried; Unistall and installed powerBI dataway but no luck.
Operating system - Windows 10 upto date. Even i am using windows 10 but able to connect Power BI dekstop successfully with my login but unable to login from his/her credentials.
Thanks,
Maru
Hi,
For what it's worth I solved my problem by logging in to Windows 10 terminal with a local account (which is exactly the same as the account of the user on the server hosting the SQL server). I have to mention that the setup is a standalone server i.e. no domains.
Hope this helps someone!
Regards.
I have the same issue but only to one Databse. After I check all the settings of this compared to other with no differences.
So I tried my luck by rename the database to shorter one (from 13 characters length name to 8 characters length name), then this was solved.
Hope this helps
I have the same issue but only to one Databse. After I check all the settings of this compared to other with no differences.
So I tried my luck by rename the database to shorter one (from 13 characters length name to 8 characters length name), then this was solved.
Hope this helps
Hello Guys,
The Error started to occur in my Power BI, in one day everything worked, in the other "Can not generate SSPI context."
My connection was always made through the Server Name \ Instance as MACHINE-NAME \ INSTANCE-NAME
So I decided to test access directly through the IP of the SQL server, and I succeeded in access!
I was successful accessing the server directly by ip which in my case is local and is on the same network as 192.168.1.1 \ instance-name
So it worked!
Anyone who is using the server on the same network can try to access over IP
I hope to help,
Regards from Brazil
I experienced a similar problem. In my case it had to to with how the credentials were stored. I had to go in to query editor, data source settings. I cleared both the data sources in current file and the global permissions setting up the connection from beginning again.
Entered with owner account to Azure SQL Server -> Go to Power BI -> press get started -> downloaded file pbids -> click on the file-> opens Power BI trying to connect -> Getting :
Details: "Microsoft SQL: The target principal name is incorrect. Cannot generate SSPI context."
P.S.
Power BI does connect to our Azure Devops account.
Any ideas how I can solve this?
