Can not connect to the sql server

[Kavi_9907]

I logged into my lap from my ad account (####\KavyaD) and login in to power bi using the same account. but due to a security concern, we have separate account to login into servers (####\KavyaDAU) . old server is ##.###.###.209 and recently bought a new server ##.###.###.6. i can go to remote desktop connection and log into the server and see the databases using ssms. and to access the server from local machine, i run the ssms by run as different user and use my KavyaDAU account to login. and i login to power bi using my ad account (KavyaD) and when i select get data and give the server ip address as ##.###.###.209, it connects. but when i add ##.###.###.6, it does not connect. gives an error saying " We encounted an error while trying to connect. Details: "Microsoft SQL: The target principal name is incorrect. Cannot generate SSPI context.". but it works for 209. then i tried login to ssms from my local machine by giving run as different user and gave credentials of my KavyaDAU account. that works for 209 but not works for 6. So there's a issue in server side not from power bi side. What should i do?

[Shahid12523]

Your new SQL Server (##.###.###.6) is missing or misconfigured Kerberos SPNs for the service account (KavyaDAU). That’s why you’re getting the "Cannot generate SSPI context" error.

Fix:
Ask a Domain Admin to run this command:
setspn -A MSSQLSvc/your-new-server-name:1433 DOMAIN\KavyaDAU

Then restart the SQL Server service on .6.

[v-tejrama]

Hi @Kavi_9907 ,

Thank you for outlining the details so clearly. Based on your explanation, the issue isn’t with Power BI, but rather with how authentication is configured for the new SQL Server. The “Cannot generate SSPI context” error indicates that the Service Principal Name (SPN) for the SQL Server service hasn’t been properly registered in Active Directory.
That explains why your old server works but the new one doesn’t, even when using your KavyaDAU account in SSMS.

To resolve this, your DBA or AD team should verify and register the correct SPN for the new server. They’ll need to identify the service account running SQL Server and ensure the appropriate SPNs are set up in AD. Once this is done and the SQL service is restarted, connections from both SSMS and Power BI should work without the SSPI error.

This should clarify the root cause and provide a clear path forward.

Best regards,
Tejaswi
Community Support Team

[v-tejrama]

Hi  @Kavi_9907 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you,

Tejaswi.

[Kavi_9907]

I shared what you have suggested with my team and they say this is happening due to someone has changed some services runing on server agent to a seperate account (#####auth) changing the default account (NT Services) and they are not aware of how to change it back to default settings.

[v-tejrama]

Hi @Kavi_9907 ,

Thank you for sharing the update. The error occurs because SQL Server services on your new server are running under a separate domain account (#####auth) instead of the default NT SERVICE accounts. When SQL uses the default NT SERVICE accounts, Windows automatically registers the necessary SPNs, ensuring authentication works seamlessly. However, with a domain account, SPN registration must be done manually in Active Directory.

To resolve this, you have two clear options. If your organization prefers using the domain account, your AD team should manually register the correct SPNs for that account and then restart the SQL service. Alternatively, if there’s no strict need for the domain account, you can revert the service to the default NT SERVICE account using SQL Server Configuration Manager, allowing Windows to manage SPNs automatically.

Thank you,

Tejaswi.

[v-tejrama]

Hi  @Kavi_9907 ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[Kavi_9907]

now database works when i open it through ssms seperately but when i import data into power bi, it gets an error. 
"Loading assembly file 'System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' failed for data source type 'SQL'"

[v-tejrama]

Hi @Kavi_9907 ,

Solution Steps:

Run Power BI Desktop as Administrator

• Right-click Power BI Desktop and select Run as Administrator
• This helps the application load system-level assemblies like System.EnterpriseServices
• Try importing your SQL data again

1. Check Local Security Policy (Impersonation Rights)
   • Open secpol.msc and go to Local Policies → User Rights Assignment
   • Make sure your user account has the following rights:
     • Impersonate a client after authentication
     • Obtain an impersonation token for another user
   • If these rights are missing, ask an administrator to grant them and restart your computer
2. Clear Cached Credentials in Power BI
   • In Power BI Desktop, go to File → Options and settings → Data source settings
   • Find your SQL data source and click Clear Permissions
   • Restart Power BI and reconnect to the data source
3. Switch to SQL Authentication (if possible)
   • If you're using Windows credentials or an alternate domain user, try switching to a SQL Server username and password
   • This often avoids impersonation issues
4. Repair or Reinstall .NET Framework
   • Check that .NET Framework 4.x is installed and functioning
   • Repair or reinstall if needed
   • Optionally, verify that System.EnterpriseServices.dll is present in the system directories
5. Update Power BI Desktop
   • Ensure you have the latest version from the official Power BI website
   • Older versions may not be compatible with some system components

Next Steps:

First, try running Power BI Desktop as administrator, as this often resolves the problem.

If that does not help, follow the steps for impersonation rights and .NET Framework repair.

If you still experience issues, collect logs and contact support with full details about your environment.

References:

Community Thread – Similar Issue