Question Re: SQLSTATE=28000 Log-on Error in Technology Q&A

SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 00:41:37 GMT

I'm getting the following error message when attempting to run an application from the development mode:

Error Code: 999

Error Message: SQLSTATE=28000

Microsoft SQL Server Native client 11.0

Invalid authorization specification.

(I've searched prior posts but I can't find one that fits my situation)

I've just migrated a PB12.5 development application on a laptop to PB 16. The laptop has Windows 8 (administrator) and the DB is MS SQLServer (Express) 2012. The 'Connect' entry for SQL Server is 'Windows Authentication'. PB 16 connects ok to the DB. The PB database profile is:

SQLCA.DBMS = "SNC SQL Native Client (OLE DB)"

SQLCA.ServerName = "TABLET\SQLExpress"

SQLCA.AutoCommit = True

SQLCA.DBParm = "TrustedConnection=1,Database='callrunner_sqlserver',Provider='SQLNCL11''

The relevant registry setting (HKEY LOCAL MACHINE and mirrored in WOW2364) are:

DBParm "TrustedConnection=1,Database='callrunner_sqlserver',Provider='SQLNCL11'"

DBMS SNC SQL Native Client (OLE DB)

ServerName TABLET\SQLEXPRESS

There is no entry of relevance in the SQL Server log file. All variables load from the registry when running/debugging the application.

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 01:27:53 GMT

Hi William;

Do you mean PB 12.6?

That is the highest release out currently.

Regards ... Chris

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 01:37:00 GMT

Thanks Chris - my mistake, I've migrated to PB 12.6

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 01:53:11 GMT

The application .EXE should connect the same as running via the IDE. Unless, its somehow not setting all the right values in SQLCA like the IDE does.

You also might like to try an SQL trace via ...

SQLCA.DBMS = "TRACE SNC"

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 02:40:34 GMT

Thanks again Chris - but exactly where do I put this code. PB Help gives no indication that I can find. I tried it under DB in the pb.ini file but was denied access.

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 02:58:17 GMT

Where ever in your application code you set the values in the SQLCA transaction object.

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 03:00:07 GMT

Tip: also try using the PB Debugger & put a breakpoint just before the DB Connect statement and see what values are in SQLCA (or what ever you named it).

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 04:58:55 GMT

I didn't have much luck. The TRACE SNS log provided the same error details as the message box.

Stepping through the connection values (SQLCA) there is no difference in the values collected than those in PB12.5 version on another Windows 8 computer.

Comparing the PB12.5 DB setup to PB12.6, the only difference I see is when setting up the IDE connection profile, selecting SQL Server 2012 from the drop-down Provider details on the Connection Tab page appends "Provider='SQLNCL11'" to the DBParm string in the DB connection syntax.

This is obviously added for some reason but, in the connection routine I have, there is no provision to get the 'Provider' details - if indeed this is necessary. Nor does it appear to be in the category of the error message of 'invalid authorisation specification'. But could this be the problem?

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 07:17:23 GMT

Hi,

Indeed this error message hints at an authorization problem or access rights.

Also what do you mean with:

>> I tried it under DB in the pb.ini file but was denied access.

Again acces denied means authorization problem. But I wonder if the user for the login has sufficient access rights for the DB.

Have you ever tried to deploy a small PB12.5 exe that just connects with the same connection string with the same user on the same computer and to the same database? Does it have the same problem? Or similar does a deployed PB 12.6 exe connect on the other computer?

Ben

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 08:12:51 GMT

Thanks for your interest, Ben.

Re the 'I tried it in the pb.ini file', that only related to where I should have set up the SQLCA.DBMS="TRACE SNC". A previous post had suggested the .ini file but Chris put me right on where it should go and I set up the trace log ok.

As you suggested, I ran a compiled PB12.5 app that uses the same DB and I got the log-on message, 'PBSNC only supports SQLNCL1 or SQLNCL10.' (I had removed the 'Provider' relating to NCL 11 from the DBParm string in the registry before running the application). That application was prepared on my desktop using MS SQL Server 2008.

To set up NCL 10, I went to the PowerBuilder IDE DB connection and selected SQLNCL10 from the Provider drop-down but got the message it was not installed.

I downloaded SQLNCL ver 10 from MS, ran the installer, but got the message, 'Not supported by the Operating system (which is Windows 8).

I would presume there would be many users developing using PB12.6 on Windows 8 and using MS SQL Server 2012, as well as running applications developed in PB12.5 or .6 and connecting to MS SQL Server 2012. Which makes me think there must be a problem in my registry settings on the computer in question.

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 08:41:32 GMT

OK;

There is not much to find on this error than check the login and password or : FIX: &quot;Invalid authorization specification&quot; error message when you run the SQL Server 2008 R2 Analysis …

Do you see anything on the server side with the profiler?

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 11:54:24 GMT

Hi William;

The Provider='SQLNCLI11' value must be added to your DBParm field in SQLCA. If you omit it, PB will default to the SQLNCLI10 driver which only supports up to SS2008. Also, if the SQLNCLI10 client is installed - omitting the Provider='SQLNCLI11' value could cause connection to not work for various reasons if there are issues with the SQLNCLI10 client install.

My suggestion is to make sure that all the same SQLCA property settings are mirrored in your Application.EXE at run time as per your PB IDE. It should then work on the same PC as your PB IDE or another PC if configured the identical way with its own SQLServer installed.

Note: Nothing you do in your Application.EXE's part of the registry or PB.INI affects the DBMS connectivity of the .EXE. They key is that the SQL Communications Area (SQLCA) is setup properly, the PBSNC126.DLL (DB interface) is deployed with your .EXE and the SQLNCLI11 client software is located in your PC's System Path.

Note2: I always turn on TCP/IP support in my SS/Express installations and use that mechanism to connect to SS vs the SQLCA.ServerName = "TABLET\SQLExpress" approach.

HTH

Regards ... Chris

Re: SQLSTATE=28000 Log-on Error

ricardojasso — Tue, 23 Jun 2015 16:05:08 GMT

William,

Is the DB in the same machine as PB or in a separate machine?

When you use Windows Authentication (Trusted Connection) PB will try to connect using your Windows user account which "SQL Server validates using the Windows principal token in the operating system" (https://msdn.microsoft.com/en-us/library/ms144284.aspx). If both PB and SQL Server are on the same machine you should have no problem connecting using the trusted connection option.

Have you tried switching to SQL Server Authentication instead of Windows Authentication (Trusted Connection)?

Regards,

Ricardo

Re: SQLSTATE=28000 Log-on Error

Former Member — Tue, 23 Jun 2015 20:31:23 GMT

Thanks for everyone's help.

I solved the problem by assigning 'sa' to SQLCA.LogID and 'password' to SQLCA.LogPass - values not required in my MS SQL Server 2008 configuration.

2008 accepted '' values for these variables with the Windows trusted connection.

(Ricardo, the 2012 DB and PB12.6 were both on a laptop)