OneDrive Blocking Connection Strings?

Question

OneDrive Blocking Connection Strings?

Paulywog0667 1

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;"}

Erland Sommarskog 77,771 Reputation points MVP

2021-02-17T22:42:48.76+00:00

So what is the problem? Is there an error message you can share with us?

And is that really VB6 and not VB .NET? If it is VB6, you need to specify the OLE DB provider to use.
Paulywog0667 1 Reputation point

2021-02-18T01:33:15.663+00:00

Hello,
Hope the screenshot shows. I assigned the static IP in ethernet settings. On the host machine the static IP works, but on my laptop to send data back to the desktop SQL, no. I enabled TCP, assigned a default port 1433 and restarted SQL. Is is just assumed write to a VB6 server or post to a page and a get through the desktop with socket connections? Seems odd SQL would allow remote connections, have a port and on a static IP machine to throw sspi errors.

Erland Sommarskog 77,771 Reputation points MVP

2021-02-17T22:42:48.76+00:00

So what is the problem? Is there an error message you can share with us?

And is that really VB6 and not VB .NET? If it is VB6, you need to specify the OLE DB provider to use.
Paulywog0667 1 Reputation point

2021-02-18T01:33:15.663+00:00

Hello,
Hope the screenshot shows. I assigned the static IP in ethernet settings. On the host machine the static IP works, but on my laptop to send data back to the desktop SQL, no. I enabled TCP, assigned a default port 1433 and restarted SQL. Is is just assumed write to a VB6 server or post to a page and a get through the desktop with socket connections? Seems odd SQL would allow remote connections, have a port and on a static IP machine to throw sspi errors.

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.

OneDrive Blocking Connection Strings?

2 answers

Activity