ODBC success at test, fails at run

[Doug Stelley]

Windows 2008

I have tclodbc installed.

in my xlate procs I have this:

package require tclodbc

set USER query

set PASSWD query

database connect sql {DRIVER=SQL Server;SERVER=snhdapp.lrjhc.dom; DATABASE=CentricityPM;Username=$USER;Password=$PASSWD}

When I test a xlate it passes without a hitch, all values returned are good.

When I put the xlate in a net config route xlate setting and try to run it I get this error:

errorInfo:

28000 18452 {[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.}

   while executing

“database connect sql {DRIVER=SQL Server;SERVER=snhdapp.lrjhc.dom; DATABASE=CentricityPM;Username=$USER;Password=$PASSWD}”

   (procedure “xlt_tribe” line 6)

   invoked from within

“xlt_tribe”

What might I be missing?

I have a DSN on this server and test it with the same username and password and it also works fine.

Topic

[Levy Lazarre]

Hi Doug,

It appears that your SQL Server is set up for mixed mode authentication.

When you run the test from the Xlate, it’s like using a command prompt and you are connecting to the SQL Server using SQL Server authentication. Therefore, your connection string works, and you get results back.

However, when you run through the Cloverleaf application, it appears that the application is trying to connect to the SQL Server via Windows authentication, using the context of the logged in user on your Windows client (hciuser? hci?). The fact that the error message returns the user as ‘(null)’ indicates that Windows is not able to pass the credentials of this user to the SQL Server.

Having said that, the first thing you should try is to add the following parameter to your connection string in an attempt to force SQL Server to use SQL Server authentication:

Code:

Trusted_Connection=False;

If this doesn’t work, then

1. Make sure the user running the Cloverleaf application on Windows has a valid DOMAIN account.

2. Make sure that this user has been granted the right to access the SQL Server from the network. Your DBA can help you with that.

Under Windows authentication, Windows will use the credentials of the logged in user to try to connect to the SQL Server. If this user is not granted access to connect to SQL Server, the request will be automatically rejected.

I hope this helps. Give it a try and let us know if it works out for you.

[Doug Stelley]

I put the Trusted Connection=false in and pretty much got the same error. (see below)

I removed all queries and all I am trying to do is connect, and then disconnect. here is the code fragment.

package require tclodbc

set gUID query

set password query

set response [database connect sql1 {DRIVER=SQL Server;SERVER=snhdapp.lrjhc.dom;Trusted_Connection=False; DATABASE=CentricityPM;Username=$gUID;Password=$password}]

echo $response

sql1 disconnect

set xlateOutVals $xlateInVals

errorInfo:

28000 18452 {[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.}

   while executing

“database connect sql1 {DRIVER=SQL Server;SERVER=snhdapp.lrjhc.dom;Trusted_Connection=False; DATABASE=CentricityPM;Username=$gUID;Password=$password}”

   invoked from within

“set response [database connect sql1 {DRIVER=SQL Server;SERVER=snhdapp.lrjhc.dom;Trusted_Connection=False; DATABASE=CentricityPM;Username=$gUID;Passwor…”

[xlt :xlat:ERR /0:testing_xlate:04/06/2011 10:21:33] [0.0.26098] Xlate ‘testonlyPID.xlt’ failed: Tcl callout error

erroCode: NONE

[Levy Lazarre]

OK. It’s still using Windows authentication despite providing the parameter. You can remove it from the string and try to investigate and fix the user permissions as suggested.

The Domain Controller is not recognizing the user sent by the Cloverleaf application, so it’s transmitting a ‘null’ to SQL Server. You have to get past this issue before your query can run.

[Doug Stelley]

I’m looking at that now.

I “think” I know what you are talking about but I’m not sure what to do about it.

The Cloverleaf engine is running under the account of .hciuser and that is local to the machine. The server itself is a member of the domain though. I’m guessing the query is trying to pass the hciuser account on to the SQL server.

I have 2 questions with this.

1) Can I change the log on as to one of our domain Service accounts that would have full rights?

or if not

2) How can I give the local hciuser account query rights on the SQL server? I went onto the SQL server and got to the security ssection of that database but wasnt sure if I could build a user based on the account of a “local” useraccount off a different box. Does that make sense?

We dont have a DBA, we wear many hats.

[Levy Lazarre]

Doug,

on 1) Unfortunately, I run Cloverleaf on Unix and I can’t tell if this alternative would not cause you issues with the Cloverleaf services.

on 2) The hciuser cannot be local only to the machine if Windows authentication is to be used. You should create a domain account for this user then go to SQL Server and give access permissions to this domain user. This is a requirement from SQL Server security.

[Doug Stelley]

Success!

I got brave and stopped the cloverleaf service. I changed the log on as to a domain service account with the rights to do what we need.

I restarted the service and what do you know, I was able to query SQL.

Thanks a lot!

We are not live yet, still in testing so I dont think anything is broke (yet…) 😀

[Author]

Replies