untrusted connection succeeds when it should fail

untrusted connection succeeds when it should fail

Post by Neil W » Wed, 04 Mar 2009 01:12:50


Using this connection string with SQL 2005 (default instance), a program is
still able to connect and execute a query, even though "me" does not exist.

"driver={SQL Server};Trusted_Connection=No;id=me;password=pswd"

Any idea what is going on here?

Thanks.
 
 
 

untrusted connection succeeds when it should fail

Post by TW9oaXQgSy » Wed, 04 Mar 2009 01:46:07

Probably because it is using trusted connection ..

"driver={SQL Server};Trusted_Connection=False;id=me;password=pswd"

I think the Trusted_Connection setting is False or True. Not Yes/No.

You can do sp_who or look at activity monitor when executing query to see
who actual is connected :).

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://www.yqcomputer.com/

 
 
 

untrusted connection succeeds when it should fail

Post by Neil W » Wed, 04 Mar 2009 02:41:20

Thanks, but I have tried it all different ways:

Trusted_Connection=False
Trusted_Connection=No
User Id=me
Id=me
Uid=me

The output connection string from SQLDriverConnect is always this!
"DRIVER=SQL
Server;SERVER=.\myserver;UID=SYSTEM;WSID=machinename;Trusted_Connection=Yes"

Is Trusted_Connection ignored in SQL 2005?

Neil
 
 
 

untrusted connection succeeds when it should fail

Post by TW9oaXQgSy » Wed, 04 Mar 2009 02:53:00

I see .. the Trusted Connection is still valid in 2005.

Examples here are use the SQL Server Native Client (new SQL Server Drives,
"SQL Server" was 2000 Drivers).
http://www.yqcomputer.com/

Did you check the Activity Monitor to see what the SQL Server is seeing?
After you connect to SQL Server from your application, bring up SSMS, connect
to SQL Server under Management -> Activity Monitor. See the connections that
exist to your database and what the SQL Server is seeing.

What programing language are you using?
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://www.yqcomputer.com/
 
 
 

untrusted connection succeeds when it should fail

Post by Neil W » Wed, 04 Mar 2009 05:14:10

Good suggestion. The Activity monitor shows this:

Process Id: 51
System Process: No
User: NT AUTHORITY\SYSTEM

As I mentioned: I am using SQLDriverConnect from a C/C++ program with this
connection string:
[driver=SQL
Server;Server=.\myserver;Id=myname;password=mypswd;Trusted_Connection=False;]

Note that "myname" is not a valid login. It successfully connects and the
resulting OutConnectionString is this (which seems to match what I'm seeing
in the Activity Monitor):

[DRIVER=SQL
Server;SERVER=.\myserver;UID=SYSTEM;WSID=machinename;Trusted_Connection=Yes]

Thanks for any light you can shed on this.
 
 
 

untrusted connection succeeds when it should fail

Post by TW9oaXQgSy » Wed, 04 Mar 2009 06:48:15

Okay, so it is using trusted connection.

Ref: http://www.yqcomputer.com/

Looks like it is ignoring your connection string properties for User Name
and Password.

According to the ref article it should be UID and PWD. The SQLDriverConnect
must wrap the actual call to SQL Server.

Giver that a try?
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://www.yqcomputer.com/
 
 
 

untrusted connection succeeds when it should fail

Post by Neil W » Wed, 04 Mar 2009 07:20:04

hanks, but it just keep getting weirder. Now the OutConnectionString is
this:
[DRIVER=SQL
Server;SERVER=.\myserver;UID=myname;PWD=mypswd;WSID=machinename;Trusted_Connection=False]

However, the "myname" login still does not exist, and the Activity Monitor
still shows user: NT AUTHORITY\SYSTEM


"Mohit K. Gupta" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...




 
 
 

untrusted connection succeeds when it should fail

Post by TW9oaXQgSy » Wed, 04 Mar 2009 08:47:01

I haven't touched C++ for a long time so not best help; but "NT
AUTHORITY\SYSTEM" means you are running under the systems authentication.
The application in question is it running as an service?

Here are few examples I found:
strcpy(tmpStr, "DRIVER=SQL
Server;SERVER=myServer;DATABASE=myDB;UID=myUser;PWD=myPwd;");
memset(maxStr, 0x00, sizeof(maxStr));
SQLSMALLINT returnSize=0;
ReturnCode = ::SQLDriverConnect(m_hdbc, NULL, (SQLCHAR *)tmpStr,
strlen(tmpStr), (SQLCHAR *)maxStr, sizeof(maxStr), &returnSize,
SQL_DRIVER_NOPROMPT );


Connection String Example:
"DRIVER={SQL Server};SERVER=hrserver;UID=Smith;PWD=Sesame"
** Note they don't mention Trusted Connection when giving UID/PWD.

Sorry I am not much help on programming side :(.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://www.yqcomputer.com/
 
 
 

untrusted connection succeeds when it should fail

Post by Neil W » Fri, 06 Mar 2009 08:20:54

I finally got it to work by putting PWD= as the last item in the string:

"driver=SQL
Server;Server=.\myserver;Trusted_Connection=False;UID=myname;PWD=mypswd"

Thanks for all your help.