SQL Server prompting for Password

[kwvh]

I have an mdb that runs on three workstations. All workstations are running Windows 7 Enterprise and Office 2007. On one of the machines the user is prompted for the SQL Server Login every time a recordset is created in VB. Yet the others are not. The others open the program, click on the control that calls VB code that creates some recordsets, updates some tables and other things and are not prompted.

FWIW - there is an autoexec macro that opens a form (hidden) based on a query where the connection, username and password are included. The form works fine on all but one machine, and prevents the user from being prompted everytime the program needs to access the tables.

Any ideas where to look? Is there a setting some place that I missed?

As always, your ideas and suggestions are greatly appreciated.

Ken

[HansV]

How is access to the SQL Server database organized? Through Windows authentication or SQL Server authentication?

[kwvh]

Thanks for getting back to me so promptly.

SQL Server authentication

[HansV]

Perhaps you should try Windows authentication - Microsoft writes "SQL Server Authentication is provided for backward compatibility. (...) When possible, use Windows Authentication."

[kwvh]

The problem with trying to convert the ODBC connection as Windows authentication is it first tries the users windows login and password. The users already have access to other SQL Servers that are authenticate via Windows. In the case of this app, the IT folks do not want to set up a group of users for this app, and expect me to authenticate via the app.
So in this case, I have a select query that queries the last record in the table and the ODBC Connection Str in the query is "ODBC;DSN=AIRR;Description=AIRR;UID=xxxxx;PWD=xxxxx;DATABASE=RISK", where x's represent the user name and password.

If I misunderstood your question, I am sorry. Thank you for your guidance.

Ken

[HansV]

I don't have much experience with SQL server, I hope someone else will have a helpful suggestion.

[geedeearr]

Hi Ken,
A couple of quick questions.....
Is it any user on the computer in question or is it only 1 certain user on that computer?
Are the users using AD/Windows security to log into the 3 computers? And are the folder permissions, where the Access db resides, the same across the 3 machines?
You said Windows 7? Is the UAT configured the same on the offending computer as the compliant ones?
Also, I don't understand "convert" in this statement:

The problem with trying to convert the ODBC connection as Windows authentication is it first tries the users windows login and password.

Thanks.

[kwvh]

I haven't tried logging anyone else in to the guilty workstation.
Yes, they are using Active Directory.
The access database resides locally on each workstation as an mde in the same folder on the local disk
UAT? I am not sure what you are asking.
"convert" is a bad choice of words. They have existing ODBC connections that authenticated with windows authentication. This particular ODBC connection is not a part of AD, and the Admin doesn't want to set them up as users in the SQL database. They assigned one user name and password, we pass those to the SQL server when we access the tables.

Thanks for your help!

[HansV]

I think Gary meant UAC (User Account Control) instead of UAT - but he'll correct me if I'm wrong.

See UAC - Change Notification Settings and the links provided there for detailed instructions about its settings.

[geedeearr]

Thanks Hans, yes I did mean UAC.

Ken>>
Sorry, still more questions.
What version of SQL?
Do you have SQL Server Management Studio (SSMS) installed on the offending computer? If so, can you connect to the database using your sql login using SSMS? (Another way to determine this connectivity is mentioned below)
Are the folder permissions set the same for all 3 computers?
Also, I guess I don't understand why you need the form with the connection string. One of the advantages of the ODBC is that the connection is always open and it is very easy to set up a "parallel" ODBC using your SQL authentication. Have you created these ODBC's with the ODBC Data Source Administrator?...Or are you connecting "on the fly"? If the ODBC's are being created during the Access App AutoExec, then would you please try and create that ODBC, using your sql login and see if the connection tests out?

Thanks.

[kwvh]

SQL Server 2005,

no, none of their workstations have SSMS installed.

folder permissions are set the same, all are set up as administrators on their pc's.

RE:don't understand why you need the form with the connection string
Because the ODBC is set up as SQL Server Authentication, the user was always prompted for a password when they opened the application. That was due to a combo box on the startup form querying the SQL Server database. Once I added the hidden form with a select query as the record source and the select query having the connection string, the users were no longer prompted as the the hidden form had passed the userid and password to SQL Server.

Without that hidden form/select query, the user is prompted for the password when they click on the combobox.

RE:Have you created these ODBC's with the ODBC Data Source Administrator
I set up the ODBC connection via "Control Panel | Adminstrative Tools | Data Sources (ODBC)" on each workstation.

I hope I have provided enough information to answer your questions. If not, feel free to ask again.

I appreciate you taking time to help. If I can't get passed this soon, I am considering creating temp tables in Access via make table queries to create temporary tables with the needed data, and then modify the recordsets in VBA to use the temp tables instead of the SQL Server tables. The temp tables would of course be Access tables and not SQL Server tables.

THANKS again for your help.

Ken

[Wendell]

I attempted to post a reply a couple of days ago but got caught in a web problem of some sort and the post was lost. The bottom line here is that the admins really should let you use integrated security. It's no more than a 10-minute task to add those users to the database in question, even if they aren't currently defined on the server hosting it. (I presume the server is joined to the domain - if not it should be, just for security sake.) I think you will find that if they do that and let you use integrated security on your ODBC connections, your problems will disappear.

[geedeearr]

Ditto to what Wendell stated on Integrated Security.

Well, 2 more things.....both rather long shots.
I've had problems with users, who are members of an Active Directory OU (Organizational Unit) and who are entered into the Administrators group on a computer, but cannot act as an Administrator because that computer is not registered in that same OU. (I hope I got the terminology correct). You could see if that is the case or not. I'd lean more towards something like this as the cause if I knew that another user, who is able to do all these things without a hitch on their computer, is not able to those things on the offending computer.

Also, you could try and delete, then re-link the tables. When you get to the Link Tables dialog box (it appears after you select your data source) and you choose the tables you wish to link, you'll see a check box to "Save password" along the right hand side.
Edit: I forgot to mention that this IS a security risk.

What version of SQL Server are you using and what OS is it installed onto?

Thanks.