How Do I Force a Password Prompt?

[pcawdron]

I'm using the SQL Server 3.70 ODBC Driver. How do I force the ODBC Driver to ask for a UserID & password?

At the moment the only way I can get the ODBC driver to work on a number of various PC's is by embedding my UserID & Password, but then the driver won't prompt the user for his/her UserID. Thus, all queries are being logged as being performed by me.

With our AS400 ODBC Driver I can simply leave the UID & PWD blank and it'll prompt the user for his/her UID & PWD. But SQL Server fails if it's left blank. With our Ingress ODBC Driver I supply a generic UserID IIPROMPTALL and it prompts the User for identification. Is there some sort of equvilant with SQL Server?

Thanks,
Peter

 

[jonscott8]

Hi Peter,

A couple of thoughts:

1. If you're using a DSN, make sure when you configure it, you select 'With SQL Server Authentication using a Login ID and password entered by the user' or else it will attempt to use NT authentication.

2. If you're using a connection string, do not pass it the UID and PWD keywords or it will attempt to use NT authentication. For example, if your connect string is as follows:
'DRIVER={SQL Server};SERVER=MySQLServer;UID=;PWD='

change it to the following:
'DRIVER={SQL Server};SERVER=MySQLServer'

3. Create your own interface to obtain the user's ID and password and then pass those in the connection string. Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[jonscott8]

Clarification:
2. If you're using a connection string, do not pass it the UID and PWD keywords if they are blank or it will attempt to use NT authentication Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[pcawdron]

Jon, thanks for your help with this. I'm still getting nowhere fast.

I've ticked the option button for "With SQL Server authentification using a login ID and password entered by the User" and NOT the "With Windows NT Authentication using network login ID" - We're still on Win 95

The next option appears to be the problem.

There's a check box with the option "Connect to SQL Server to obtain default settings for the additional configuration options." Directly below this is the Login ID & Password textboxes but they're disabled when this option is not checked.

If I proceed past this point without checking the box, leaving the UserID & Password blank & disabled - the test connection button fails. If I check this box and enter my User name & password the test connection succeededs. If I check this box I cannot proceed without supplying a valid UserID & password.

There must be some kind of generic UserID that forces a prompt, like the Ingress IIPROMPTALL

Any ideas?

Thanks,
Peter

 

[jonscott8]

Hi Peter,

Check that box and input a valid Username & Password.(FYI - The DSNs I use have my DBAdmin username and password). AFAIK, This is used only to establish the DSN. As long as you omit the UID & PWD keywords when establishing the connection, ODBC will prompt for Login.

FWIW - how are you establishing a connection to the server? ADO or DAO? SPT? What are you using on the front-end? If you are establishing your connection via code, post it here.

If all else fails and time is a priority, consider option #3 above. Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[pcawdron]

I'm using VB code to connect to our AS400 & Ingress DB's but when it comes to SQL Server I'm using a reporting tool called BusinessObjects - the UID & PWD is not something I can pass via code.

This is why, what I'm really after is trying to find a generic prompt (Like the Ingress IIPROMPTALL) that forces the SQL Server ODBC driver to produce a dialog box requesting UID & PWD.

Sorry for not being clearer on this in the first place.

Thanks for your input,

Peter

 

[jonscott8]

LOL

Coincidentally, the company I work for just recently purchased BO. It might be a few weeks before I get into it, but if I can find out anything, I'll let you know.

FWIW, it might help if you post as much detail about your configuration. Are your users connecting straight to SQL Server using freehand SQL or do you have a Universe built, etc..(I'll presume your not establishing your connection thru VBA because you said "is not something I can pass via code&quot. Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[pcawdron]

It's through a Universe. We've had BO up and running for about a year now and it's extremely helpful.

I've done some stuff in BO VBA, data cubes, etc. It's great program. The hardest part about using it is getting your report to look half-way decent.

Regards,

Peter
pcawdron@gmac.com.au