SQLSTATE=28000 Log-on Error

[Former Member]

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.

[Former Member · Accepted solution]

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)

[ricardojasso]

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

[Former Member]

Hi William;

  Do you mean PB 12.6?

That is the highest release out currently.

Regards ... Chris

[Former Member]

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

[Former Member]

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"

[Former Member]

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.

[Former Member]

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

[Former Member]

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).

[Former Member]

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?

[Former Member]

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

[Former Member]

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.

[Former Member]

OK;

There is not much to find on this error than check the login and password or : FIX: "Invalid authorization specification" error message when you run the SQL Serv...

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

[Former Member]

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