OneDrive Blocking Connection Strings?

Question

Hello,
I've been able to accomplish a basic content management system on my local machine in vb6 and sqlDeveloper. I have a static IP from my ISP, but only the machine that will be the cloud can connect. It's been over a decade since we discussed connection strings and those were in vba (Access). Here is my example connection string. Is the OneDrive login from the remote laptop basically messing up authentication? I'm gussing I missed something in setup of sql.

SQLCon As New SqlConnection With {.ConnectionString = "Data Source=staticIP,SQLport;Network Library=DBMSSOCN;Initial Catalog=DatabaseName;Trusted_Connection=Yes;"}

Answer 1

Hi Paulwog0667-0873,

The target principal name is incorrect. Cannot generate SSPI context.

This issue might be related to Kerberos authentication. And the common reason for this error is the SPN problem. If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos authentication interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local account do not have the right to set their own SPNs. Please check whether SPN's are correctly registered. You can use “SETSPN -L <ServiceAccount>” in cmd to see what SPNs are listed for a particular account. The SPN should look like this: MSSQLSvc/<FQDN>:<port> If the SPN is not configured, you need to manually create an SPN for your computer that is running SQL Server. Please refer to How to troubleshoot the "Cannot generate SSPI context" error message and Register a SPN for Kerberos Connections which might help.

Best Regards,
Amelia

Answer 2

You are using Trusted_connection=yes, and while this is preferable, it does not always work. Windows authentication requires that the client (that is, your laptop) and SQL Server are in the same domain. I guess that if you have your SQL Server instance at an ISP and you are connecting from your laptop from home or your office, you are not part of their domain.

In this situation you need to use SQL authentication. That is, you need to replace Trusted_connection=yes with Username=user;Password=pwd.

You will need to create a login on the server that you can use. Also, the SQLServer instance must be configured to permit SQL authentication. You do this from Object Explorer, by right-clicking the top node for the server and select Properties and then the security page. You need to restart SQL Server for the change to take effect.