- Remove From My Forums
-
Question
-
Hi All,
I am stuck in a strange issue post installation of SQL Server 2017 Standard.
I installed a Core licensed SQL Server 2017 Standard Edition on a Server with Mixed Mode Authentication. Set up was executed successfully.
While I am connected to my network through VPN Connection. I am able to connect with Server through SQL Authentication However with Windows Authentication it does not connects and fails out with message as «The target principal name is incorrect.
Cannot generate SSPI Context.»However when i tried to connect through LAN with Windows Authentication it let me connect without any issues.
Can you please help me in resolving the connectivity issue over VPN for Windows Authentication.
Below are the error details
===================================
The target principal name is incorrect. Cannot generate SSPI context. (.Net SqlClient Data Provider)
——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476——————————
Server Name: 000.000.00.00
Error Number: 0
Severity: 11
State: 0
Procedure: GenClientContext——————————
Program Location: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, SqlAuthenticationProviderManager sqlAuthProviderManager).
.
.
.
Thank You.
Best Regards, Sunil Sharma
-
Edited by
Thursday, April 23, 2020 12:25 PM
-
Changed type
pituachMVP
Friday, April 24, 2020 5:58 PM
not a question -
Changed type
pituachMVP
Friday, April 24, 2020 5:59 PM
mistakenly changed the type before
-
Edited by
Answers
-
Hi Sunilsharma,
>The target principal name is incorrect. Cannot generate SSPI context. (.Net SqlClient Data Provider)
This is a typical Kerberos authentication failure. There are various reasons for this error. The most common one is the SPN problem. Many cases of this error have been found due to abnormal KDC operation or abnormal TGS service. You can reference: how-to-troubleshoot-the-cannot-generate-sspi-context-error-message
You can also try workaround as next:
If you use your domain account to log in, you should grant the account rights of Read and Write SPN, and then restart the server with this account.
1.Make sure TCP/IP Protocols are enabled and configured correctly;
2.Close the firewall;
3.In your DC, run->”adsiedit.msc”
4.Assume that the start account is YXAdministrator, Administrator->Properties->Security->Advanced->Permissions->Add->Select a principle->Input “SELF”->OK (as next screenshot shows)
5.Choose “Read serverPrincipalName” and “Write serverPrincipalName” (as next screenshot shows)
6.Use this account restart your server and browser;
Note:
You need to note that when solving Kerberos-related problems, you may encounter this situation: clearly all the conditions required for Kerberos are configured.
OK, but you still get Kerberos errors or NTLM errors when you test the connection. At this time, you may wish to try the following two tricks:(1) There may be multiple DCs in a domain environment, and the series of changes you make during the investigation will only affect one of the DCs. maybe
You can use another DC to connect the client to SQL Server, but this DC has not been synchronized to the series of changes you made before.
At this time, you do not need to wait for automatic synchronization between DCs to occur, you can run the following statement to force synchronization between DCs:
Repadmin / syncall(2) Credential Cache may also be a problem. Credential Cache is used by Kerberos to cache authentication information on this machine. It mainly contains TGT and Session tickets. Since Credential Cache has a life cycle (usually 10 hours) on the machine, if the
client has received incorrect authentication information and cached it, it will use this information to access SQL Server until the cache expires. So you will always get errors. The solution is to clear the Credential Cache by any of the following three methods.
1) Use the klist.exe purge command
2) Use kerbtray tool
3) Restart the entire machineBest Regards.
yuxi
MSDN Community Support
Please remember to click «Mark as Answer» the responses that resolved your issue, and to click «Unmark as Answer» if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com-
Proposed as answer by
pituachMVP
Friday, April 24, 2020 6:01 PM -
Marked as answer by
Sunilsharma
Thursday, April 30, 2020 3:36 PM
-
Proposed as answer by
-
What
yuxi666 mentioned is really good!What i can add… check if the SPN for SQL has been added to the SQL Service account.
1. On your SQL Server, open SQL Server Configuration Manager
2. Look at the Log On As column for the SQL Server service.
3. Open a command prompt and type the following command:
setspn -l [Log on account]
For example, if the log on account is Domainsvc-sql the command line would be
setspn -l Domainsvc-sqlIf the account is NT AuthorityLocal System, the account is the computer account. Then the command line would be
setspn -l [SQL_Computername]The SQL SPN should look like this: MSSQLSvc/[SQLServerName]:Port
If the SPN is not configured, that should explain why you receive the error Cannot generate SSPI context
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver15
hth
This posting is provided AS IS without warranty of any kind
-
Proposed as answer by
pituachMVP
Friday, April 24, 2020 6:02 PM -
Marked as answer by
Sunilsharma
Thursday, April 30, 2020 3:36 PM
-
Proposed as answer by
-
Hi Sunilsharma,
This may be one network issue.
Could you please upload the error log here?
the path is:
Program FilesMicrosoft SQL ServerMSSQL.nMSSQLLOGERRORLOGandERRORLOG.nPlease check whether this is helpful:
trusted-connection-to-sql-fails-after-connecting-to-a-vpn, connect-to-sql-via-windows-authentication-over-vpn
Best Regards.
yuxi
MSDN Community Support
Please remember to click «Mark as Answer» the responses that resolved your issue, and to click «Unmark as Answer» if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com-
Marked as answer by
Sunilsharma
Thursday, April 30, 2020 3:25 PM
-
Marked as answer by
by Tashreef Shareef
Tashreef Shareef is a software developer turned tech writer. He discovered his interest in technology after reading a tech magazine accidentally. Now he writes about everything tech from… read more
Published on October 12, 2020
- The error cannot generate SSPI context can prevent the admin and users from accessing their SQL server.
- In this article, we explore the three ways to fix this error to get you back online.
- If the issue persists, check out our dedicated SQL Server page for more fixes.
- You can also explore our Troubleshooting Hub for more helpful solutions.
XINSTALL BY CLICKING THE DOWNLOAD FILE
This software will keep your drivers up and running, thus keeping you safe from common computer errors and hardware failure. Check all your drivers now in 3 easy steps:
- Download DriverFix (verified download file).
- Click Start Scan to find all problematic drivers.
- Click Update Drivers to get new versions and avoid system malfunctionings.
- DriverFix has been downloaded by 0 readers this month.
The target principal name is incorrect – cannot generate SSPI context error occurs when trying to make a connection on SQL Server from a remote server with a Windows account.
This is a generic error. It can be triggered due to many reason including an outdated password, clock drift, failure to register an SPN, or Active Directory access permission.
In this article, we take a look at a few troubleshooting steps to help you resolve the cannot generate SSPI context error on the Windows server.
How can I fix the target principal name is incorrect – cannot generate SSPI context error?
1. Change SQL Service User
Try changing the SQL SERVICE user with the one that is Domain Admin. When you shut down the service, you need an account with privileges to create a new SPN (Service Principal Name).
When a service starts without it, it will trigger the error. Changing the privileges of your system account can fix the error.
However, it is always recommended for service accounts to give them the least privileges due to security reasons.
Remove the SPN entries from AD Users and Computers
- Open the Active Directory User and Computers in Advanced View.
- Look for the SSPN entries for MSSQL Svc.
- Remove all the entries associated with MSSQL Svc.
- Close AD User and Computers and check for any improvements.
- Change Active Directory permission.
2. Check your password
The error cannot generate SSPI context can occur due to password issues. If you had recently changed your password, but haven’t logged out of your account, it can trigger the error.
Try logging out and then signing in with the new password to fix the error.
In other instances, the issue could be due to password expiration. Change the expired password and login with the new credentials to see if that resolves the error.
3. Change Active Directory permission
- Run Adsiedit.msc and from the Run dialog box.
- In the Active Directory Service window, expandDomain [YourDomainName], then expand DC = RootDomainName, and then CN = Users.
- Right-click on CN= [YourAccountName] and select Properties.
- Open the Security tab.
- Click on Advanced option.
- Select any one of the SELF rows.
- Click Edit and then the Open Permission Entry window.
- Here, make sure the Principal is set to SELF, Type is set to Allow, and Applied to is set to This Object Only.
- In the Properties section, select the following.
Read servicePrincipalName
Write servicePrincipalName - Click OK to apply the changes and exit.
Try establishing a new connection and check if they cannot generate SSPI context error is resolved. Make sure you restart the SQL Services that are associated with the current account to apply the changes.
Changing the Active Directory permission is a safe option than changing the SQL server user. However, before you proceed to change the permission, make sure the problem is triggered due to permission issues.
Log in to the server where your SQL instance is running and then check the error logs to check if the error is triggered due to permission problems.
The error in the log will look something like this:
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/servername.domainname.net:1433 ] for the SQL Server service.
Windows return code: 0x2098, state: 15. Failure to register an SPN might cause integrated authentication to use NTLM instead of Kerberos.
This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
The error cannot generate SSPI context can occur due to permission as well as expired credentials issues. Changing the password and permission should help you fix the error and log back into your SQL server.
Still having issues? Fix them with this tool:
SPONSORED
If the advices above haven’t solved your issue, your PC may experience deeper Windows problems. We recommend downloading this PC Repair tool (rated Great on TrustPilot.com) to easily address them. After installation, simply click the Start Scan button and then press on Repair All.
Newsletter
June 18, 2019 by Sql Times
Interesting Problem Today:
Ran into this issue a few times and every time its a variation of the same headache. So, here some ideas will be documented for posterity.
In general terms, the error looks like this:
Connecting to Subscriber '' Agent message code 20084. The process could not connect to Subscriber '' Microsoft SQL Server Native Client 11.0 SQL Server Network Interfaces: The target principal name is incorrect. Cannot generate SSPI context
The error message seems nebulous and confusing — but for trained eyes it makes perfect sense. For me it took a while to make sense out of it.
There could be several things wrong under the hood, but essentially it says that the target SQL server that is is trying to connect to, does not have a valid SPN with Active Directory.
Meer from Microsoft has documented some information on troubleshooting this issue here. For more details, please read his article, as I’ll over simplify things and address a variation of the problem in this articles (which will be slightly different from his).
Example:
From SQLServerA, using UserA, if I’m trying to connect to SqlServerB, sometimes I get this error. Essentially, means SqlServerB does not have a valid SPN.
Resolution:
First, log into the machine that has SqlServerB. Open command prompt with Administrative privileges. Run the command below to see if there is a valid SPN.
SETSPN -L <SQL Server Instance Service Account>
If the output looks like the first image below, then the Sql Server instance does not have a valid SPN. Now its time to generate one.
Output without valid SPN:
Invalid SPN
Step 2: Download Kerberos Configuration Manager for SQL Server from here, and start generating one.
Step 3: Open Kerberos Configuration Manager for SQL Server from the same machine that has SqlServerB instance. When you run it, it shows something like this:
Kerberos Tool Output
Notice that for Sql Server service, there is not valid SPN or misplaced SPN. So its time to generate one.
Step 4: Hit the “Fix it” button right next to it and generate one. Make sure the user account that is logged into the machine has domain controller permissions.
Step 5: Now run the same command as in Step 1, and the output looks different.
Output with valid SPN:
Valid SPN
Voila !! Now you are able to connect to SqlServerB from SqlServerA using UserA
Posted by in SQL Server, SQL Tips and Tricks
on Jan 12th, 2018 | 5 comments
One of our old SQL servers was running under the local system context. Then we decided to change the account that the SQL service runs under, and we created domain service account with basic domain user permissions.
Eventually, we end up with following error trying to access our SQL Server remotely.
SQL Server SPN Creation
To run SQL Server service you can use Local System account, local user account or a domain user account. If you are using Local System account to run your SQL Service the SPN will be automatically registered. Nevertheless, if you are using domain account to run SQL Server Service and you have domain user with basic user permissions (In our case) the computer will not be able to create its own SPN.
In case you are using domain administrator account, you will not have any problems.
SPN will be successfully created since domain account you are using to run SQL Server Service will have domain administrator-level credentials.
There are a couple of solutions for this problem.
- You can elevate permissions and use domain admin account for your SQL Server Service (Not recommended).
- You can manually create an SPN for your computer that is running SQL Server and assigned that SPN to the service account of the SQL Server service on that machine.
- You can use basic domain user account with elevated permissions (Write all properties, Write msDS-PrincipalName)
Pay attention that you can have only one SPN and must be assigned to the appropriate domain/local account (current SQL Server service account)
Manually create SPN
There is really good article by Microsoft how to configure SPN for SQL Servers
How to Configure an SPN for SQL Server Site Database Servers
- Open cmd and list your current SPNs
setspn -l servername
SPN for the NetBIOS name of the SQL Server will look like: MSSQLSvc/SQLServerName:1433
SPN for the FQDN of the SQL Server will look like: MSSQLSvc/SQLServerFQDName:1433
In my case I have just default instance, So I need to change just those with 1433 port number. If you have named instance port number depends on previous SQL Server configuration.
- To change the SQL Server service account from local system to a domain user account remove current SPN from MSSQLSvc/SQLServerName:1433 computer account and add to the domain account.
setspn -D MSSQLSvc/SQLServerName:1433 SQLServerName
setspn -A MSSQLSvc/SQLServerName:1433 DomainAccount
- You can verify domain user SPN is registered correctly with the following command
setspn –L DomainAccount
Write all properties permissions, Write msDS-PrincipalName
Another option is to elevate permissions for domain user you are using to run SQL Server Service. Of course, you will need AD access to accomplish this.
- Active Directory Users and Computers (With Advanced Features Enabled)
- Select User and choose properties
- Select Security TAB
- Select Advanced settings
- Add new permission entry
- Choose “Read all properties” permission
- Select “Write msDS-PrincipalName” properties
Those permissions should be enough to allow that domain user to create SPN.
Similar Posts:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-07-2020
10:09 PM
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?
Message 18 of 18
10,877 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-22-2017
02:49 AM
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
Message 12 of 18
18,531 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-12-2017
11:16 AM
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
Message 13 of 18
18,489 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-09-2020
12:31 PM
This worked for me, but I had to initially create the excel file with the query on a different pc than mine and I could do it with using the server name instead of the IP address. After I did that, I could then open the query editor on my machine and change the source to the server IP address.
I cannot create a new database connection on my machine using the IP address. I must create it on a different machine first. Anyone have an idea as to why that would happen?
Thanks,
Greg
Message 16 of 18
10,502 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-17-2018
12:00 AM
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.
Message 14 of 18
17,893 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-22-2017
02:47 AM
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
Message 11 of 18
34,431 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-19-2016
08:00 PM
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 Zhang
Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 6 of 18
48,511 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-30-2016
05:52 AM
Hi,
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
Message 7 of 18
48,087 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-02-2019
02:13 AM
Did you find resolution for this? If Yes, please let me know what steps you have taken to resolve.
@marumsdax wrote:
Hi,
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
Message 10 of 18
16,329 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-30-2016
09:54 AM
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.
Message 8 of 18
48,083 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-18-2017
06:30 PM
I have the same issue here — but it comes and goes as it pleases… Ive been refreshing an SQL on a remote server fine for 6 months, then tonight i get this message…. Tomorrow it will work fine.
Message 9 of 18
35,848 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-19-2016
05:54 AM
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
Message 4 of 18
48,526 Views
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-18-2016
09:20 AM
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.
Message 2 of 18
48,556 Views