• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • CONNECT fails once when using TrustedConnection=1 for MSSQL thru ADO.NET

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved CONNECT fails once when using TrustedConnection=1 for MSSQL thru ADO.NET

  1. How-to
0
Votes
Undo
  1. Michael Kramer
    Michael Kramer
  2. PowerBuilder
  3. Monday, 1 October 2018 15:42 PM UTC

Hi, any of you seen similar behavior where CONNECT using Windows authentication requires repeated CONNECT attempts?

 

What I have:

Setup  ::  PB 2017 R3, #1858 using ADO.NET with MSSQL.
Code  ::  See below

SQL Server Authentication  ::  (using LogId + LogPass)  ==> Successful each time
Windows Authentication  ::  (using TrustedConnection=1)  ==>  Fails often, but repeating succeeds each time.

...
this.DBMS = 'ADO'
this.DBParm = "NameSpace='System.Data.SqlClient'"
this.DBParm += ",DataSource= ..."
...
this.DBParm += ",TrustedConnection=1"
this.LogId = ''
this.LogPass = ''
...
CONNECT USING this;
if this.SQLCode <> 0 then
   ...

 Error info here is as follows:

this.SQLCode = -1
this.SQLDBCode = 999
this.SQLErrText = System.Exception: Failed to get current user name.
at Sybase.PowerBuilder.Db.Block.SqlBlock.Connect(Object[] input, Dbms& dbms, DBVersion& dbVer, String& user)
at Sybase.PowerBuilder.Db.DbRoutine.Connect(DbDrivers type, Object[] input, Dbms& dbms, DBVersion& dbVer, String& user)

It is the CONNECT statement itself that fails with this error message. No embedded SQL form the PowerBuilder app has executed yet.

First attempt at CONNECT using TrustedConnection has approx 50% success rate.

Second attempt has so far had a 100% success rate. We need first attempt to show same success rate.

Any suggestions greatly appreciated!
/Michael

Connecting to Github repository other than Master ...
How do I change “Microsoft Web Browser in OLE cust...
Responses (2)
David Peace (Powersoft)
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Tuesday, 2 October 2018 12:50 PM UTC
  2. PowerBuilder
  3. # 1

Hi Michael

That's an odd one, it says failed to get current user name. If that were the case why does it work next time.

My initial gut reaction is a timing issue, the first call times out on domain authentication somewhere and the second works because the back-end services have been woken up. All a bit woolly I know, but intermittent problems would imply something like that.

I tried googling for the error but after drilling in to precisely your error I ended up back at this page, so that didn't help.

There are logs on the SQL Server that might give more information about the error that is occurring, perhaps that might help.

 

Regards

David

 

Comment
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Wednesday, 3 October 2018 15:56 PM UTC
  2. PowerBuilder
  3. # 2

Thanks David, I added TRACE to compare success vs. failure and found that the failure happens so early, it may even be client-side requesting Windows ID and not the server. Unfortunately I can't look at source code for the ADO adapter so it feels like fumbling in the dark.

Here are my traces my identifying data masked out. I highlighted differences.

 

TRACE SQL Server Login - Successful

SQL no 1: CONNECT USING this:
(00112233): LOGIN:(DBI_LOGIN) (9.999 MS / 9.999 MS)
(00112233): CONNECT TO TRACE ADO MS SQL VIA ADO.NET:
(00112233): LOGID=…
(00112233): DBPARM=Namespace='System.Data.SqlClient',DataSource=…,Database=…,…(DBI_CONNECT) (0.003 MS / 9.999 MS)
(00112233): (DBI_ADJUST_CONNECT) (0.000 MS / 9.999 MS)
… and more trace before SQL no. 2 from PB app 

 

TRACE Windows Login - Successful

SQL no 1: CONNECT USING this:
(00112233): LOGIN:(DBI_LOGIN) (9.999 MS / 9.999 MS)
(00112233): CONNECT TO TRACE ADO MS SQL VIA ADO.NET:
(00112233): DBPARM=Namespace='System.Data.SqlClient',DataSource=…,Database=…,…,TrustedConnection=1(DBI_CONNECT) (0.004 MS / 9.999 MS)
(00112233): (DBI_ADJUST_CONNECT) (0.001 MS / 9.999 MS)
… and more trace before SQL no. 2 from PB app 

 

TRACE Windows Login - Failing

SQL no 1: CONNECT USING this:
(44556677): LOGIN:(DBI_LOGIN) (8.888 MS / 8.888 MS)
(44556677): *** ERROR 999 ***(rc -1) : System.Exception: Failed to get current user name.
at Sybase.PowerBuilder.Db.Block.SqlBlock.Connect(Object[] input, Dbms& dbms, DBVersion& dbVer, String& user)
at Sybase.PowerBuilder.Db.DbRoutine.Connect(DbDrivers type, Object[] input, Dbms& dbms, DBVersion& dbVer, String& user)
(44556677): SHUTDOWN DATABASE INTERFACE:(DBI_SHUTDOWN_INTERFACE) (0.000 MS / 8.888 MS)


APP CLOSED BY USER

Comment
David Peace (Powersoft)
  1. David Peace (Powersoft)
  2. Friday, 5 October 2018 14:52 PM UTC
Not enough to make any conclusion from those logs. Is it possible to get a more details log fro the SQL layer.
  1. Helpful
Michael Kramer
  1. Michael Kramer
  2. Friday, 5 October 2018 15:15 PM UTC
Hi David. I found the reason and fixed it.

First hint towards resolution was that deployed EXE seemed to have NO issue while Run-from-IDE failed about every second time it ran.

CAUSE: Missing DISCONNECT in app's Close event. I don't know why I didn't look there but issue on CONNECT seldomly has me look for clues in app.Close.

RESOLUTION: DISCONNECT when app closes.



It seems that when NOT disconnecting, the DB connection is hanging in some undefined state in the context oft he Windows process running the PB IDE. Next CONNECT attempt fails but "crashes"/invalidates the old connection. Therefore next CONNECT attempt succeeds because the old connection has been recycled.



Reason for not seeing this in the deployed EXE is that when that EXE closes, the full Windows process ceases. Hence, next run of the EXE starts a new process and therefore retrieves a fresh connection each time.



CONCLUSION: I (and everyone else) should always follow good design and coding practices: Always DISCONNECT what you CONNECT'ed, always Close what you Open'ed, and always release what you acquired.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.