ODBC Cannot generate SSPI Context

Question

ODBC Cannot generate SSPI Context

AVB

SSChampion

Points: 11418
More actions
January 24, 2011 at 4:50 pm

#72955

I have a remote computer that is using a one-click application to access our SQL 2000 DB server.
The application uses a DSN (TCPIP) setup thru the ODBC utility. The DSN works fine as the communication tests are successful. The SQL user login that is used is also able to login through QA,EM etc.. and access the DB. Also the DSN is not using Windows Authentication.
When we try to access the DB using the DSN through the application we get an Error: "HY000 Cannot generate SSPI context".
I've been digging around the web and this site and have read quite a bit about this issue. With that I've walked through some of the steps the articles suggested for resolving this issue.
I tested resolution of the Public FQDN successfully and we don't have any SPNs setup so it should (according to one article) force NTLM over TCPIP.
I'm just not sure why the DSN Tests work but when it's used in the app communication "fails".
I also wanted to add that the DSN will work if it is setup on a machine local to the DB server. In this case the DSN uses the netbios name instead of an external IP or FQDN.
Any suggestions? TIA.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

You don't mention which provider you are using, but I had a similar problem recently trying to drag data from SQL 2008 into Excel 2010.

Setting up the connection worked fine, testing it worked fine, but trying to use it in Excel came up with the SSPI error from the named pipes provider - which I was not using!

I fixed my problem by specifying the SQLNCLI provider instead of the SQLOLEDB or ODBC providers - now I realise you are using sql 2000, but AFAIK the sql 2008 native client will talk to that quite happily and may be an option.

I don't know why this problem started happening, nor why the change to SQL Native Client fixed it, but it may be worth a try.

(I suspect that either a M$oft or 3rd party update has messed with OLEDB but have no proof!)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

AVB SSChampion Points: 11418 More actions · Answer 2

mister.magoo,

Thanks for the reply.

If you mean what protocol (provider) I'm using... I am using TCP/IP unless you're referring to ODBC which we are using. Not sure if I'm understanding you correctly. It's a C# application that calls the DSN you setup through the ODBC utility of windows. The app queries the ODBC utilty and lists all the DSNs created on that machine. Then the app builds the connection string based on the DSN name you select. My DSN I created is only using TCPIP.

Weird thing is I can get it to work if I create a DSN that is on a machine local to the DB Server (same network and domain) and uses the same username/password credentials. The only difference is the DSN is calling the netbios name of the DB Server and not an external FQDN. Unless it's firewall related... From what I read SSIP info has to do with authentication and maybe that happens on a separate port from the SQL stuff.

I'm unsure how to specify the SQLNCLI but the application is built in-house and is currently under development so we can make changes to the app. How do you specify SQLNCLI with a DSN? or within an app? Any idea?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

When you set up an ODBC using the ODBC control panel applet, the first thing you choose is the Driver.

Here you can choose SQL Native Client if you have it.

That is what I mean.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

AVB SSChampion Points: 11418 More actions · Answer 4

SQL Server is the only driver available. I see I can download the Native Client and it does work with SQL 7.0, 2000 and 2005... at least this version does: SQL Native Client (9.00.1399.06)

http://support.microsoft.com/kb/910016

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

Yes, that is the one, give it a try if you can - I can't promise it will help, but hopefully...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

AVB SSChampion Points: 11418 More actions · Answer 6

I downloaded the Native Client 10.0 and used it but no luck.

I can't connect to our DB using a DSN within our application unless we're on the same network and logged into the domain.

I'm not sure we're calling the DSN the proper way within the application.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Ok, so can you show the settings for the DSN - change any sensitive information - and how you use it in the application?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

AVB SSChampion Points: 11418 More actions · Answer 8

I've tested the DSN with various settings.

The DSNs use:

SQL Server Driver

For the server I've used Public or Internal IP addresses and FQDNs depending if I'm local or not. All can be resolved.

TCPIP (Dynamically Determine Port)

Server Name and Alias are the Same

SQL Login or Windows Authentication (I've tried both)

The DSNs Connectivity Tests are always successful.

The connection string looks something like this:

cn.ConnectionString = "Dsn=DSNName";Trusted_Connection=yes";

If we comment out Trusted_Connection = yes then we get a Login failed for user 'null'...

When we have Trusted_Connection = Yes then we get the SSIP errors as well as a Local Security error.

The other thing I noticed was when I was connected locally to the domain and when we had Trusted_Connection = Yes I could access the DB with a DSN however that DSN had an SQL Login specified but that was ignored and my Windows Credentials were used. This was observed in SQL profiler.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

AVB (1/25/2011)
I've tested the DSN with various settings.
The DSNs use:
SQL Server Driver
For the server I've used Public or Internal IP addresses and FQDNs depending if I'm local or not. All can be resolved.
TCPIP (Dynamically Determine Port)
Server Name and Alias are the Same
SQL Login or Windows Authentication (I've tried both)
The DSNs Connectivity Tests are always successful.
The connection string looks something like this:
cn.ConnectionString = "Dsn=DSNName";Trusted_Connection=yes";
If we comment out Trusted_Connection = yes then we get a Login failed for user 'null'...
When we have Trusted_Connection = Yes then we get the SSIP errors as well as a Local Security error.
The other thing I noticed was when I was connected locally to the domain and when we had Trusted_Connection = Yes I could access the DB with a DSN however that DSN had an SQL Login specified but that was ignored and my Windows Credentials were used. This was observed in SQL profiler.

Excuse me if I am telling you something you already know and have tried, but from your notes here it seems like you haven't....

You obviously do need to remove the "Trusted_Connection=Yes" as it forces Windows Authentication, but in it's place the application needs to supply a sql login and password.

The DSN allows you to enter a login and password but it only uses that to connect to the server during setup to get default database settings (and maybe some other settings).

What I would suggest is that you (for testing the connectivity) change the connection string in the application to avoid the DSN just for the moment.

Try something like this:

Provider=SQLOLEDB.1;Password=my_password;Persist Security Info=True;User ID=my_sql_login;Initial Catalog=my_database;Data Source=MYSERVER

If that works, then you know that it is the connection string/DSN that is the problem.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

AVB SSChampion Points: 11418 More actions · Answer 10

MM,

Again thanks for the reply.

The application already has one DSN-less connection string that connects to our DB for doing other stuff. That connection string has the username and password supplied which is an SQL Login. It also has Trusted_User = Yes. This works without any issues (externally, internally etc...).

Here is what we are doing with our app and the DSN:

The app will present the user with a dropdown list of their DSNs. The user chooses the DSN they want to use and saves that setting. The app will then use that DSN to query the DB and list tables/views. The user will then map fields from the tables/views to other fields in our application.

The application is dynamically creating a connection string from the DSN.

(Just had an ah-ha lightbulb moment after some reading)

What I didn't know and just found out was that DSNs don't store passwords. I thought they did so when the DSN was referenced it should use those values (but they don't exist so it won't). I wondered why I had to provide my password every time I configured an existing DSN with SQL Login.

What was also throwing me off is sometimes we'd test using a DSN with windows authentication and everything would work (no passwords in the connection string)... but for an SQL Login we have to provide the Username/Password in the connection string.

AVB SSChampion Points: 11418 More actions · Answer 11

MM,

I was able to get the DSN to work with our application locally and remotely. We prompt the user for their username/password and use that to login. It works well connecting to our DB. However we are now running into another issue on a different network and DB.

We are getting the error: "TCP Provider, error:0 A connection attempt failed b/c the connected party didn't properly respond after a period of time..."

The PC that is getting this error has a DSN that is successful in it's Connection Test. We can also access the DB via QA, EM from the same machine (using the same SQL Credentials the DSN is using) but when we reference the DSN within our app it doesn't work. The PC is running Vista and the SQL Server is 2000.

Any ideas why it would flake on another network? I also wonder what the differences are between the various connection types (DSN Test Connection ODBC Ping?, EM/QA and our connection string).