Bypass login sa and still use sql authentication

Question

Post reply

Bypass login sa and still use sql authentication

suha hammad

SSC Enthusiast

Points: 121
More actions
October 4, 2005 at 3:37 am

#169123

i have adp msaccess project linked to sql server database. i am using sql authentication. when running the application i have a separate form asking user to enter his ID and password.
I open the connection using the connection string
cn.openconnection ....
and supply the user name sa and its password hard coded in the connection string and it works fine.
When i run the application it always asks for login sa and its password and when pressing escape the application opens using the connection string without problems.
The problem is that i don't want the system to ask me for the login sa and its password and go ahead to run the application using my connection string.
Can anyone help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32345 More actions · Answer 1

1) Are you using linked tables? If you, was the password saved when making them

2) Are you using DSN-less connections?

see http://www.connectionstrings.com/ for a list of connectionstrings.

3)You shouldn't use sa for any program (full control over your sql server). Make a specific user.

chappu SSC Enthusiast Points: 103 More actions · Answer 2

As Jo stated I would not recommend using sa in any of your applications.

If you are using an adp, then you should not need to link tables. By connecting to the SQL Server you will see all the tables, views, sps etc from within access.

With regards to your question, it depends on how your app is written but you should connect using the user/pass supplied instead of sa and then use roles on SQL server to control the users priviledges. If a user then presses cancel when logging, it will not connect as no login details are provided.

Sean Fackrell SSC-Addicted Points: 438 More actions · Answer 3

You are getting prompted for sa because of the settings of the property CurrentProject.Connection.ConnectionString. This can be changed to use NT authentication using File - Connection. This always gets read when you open the ADP even if you hold down the shift key.

You probably want to disconnect your project which you can do in code:

CurrentProject.OpenConnection ""

And then you won't be prompted when you first open the project. Then connect using your form and its code.

suha hammad SSC Enthusiast Points: 121 More actions · Answer 4

Thank you very much Sean and thanks to all who replied .

when using windows authentication using file - connection , the application does not prompt for the user name only the first time i run the application, the next time it prompts!

when omitting the connection string from currentproject.openconnection , the system don't prompt for the user name the next time i login.

but still some commands needs the currentproject.openconnection connectionstring.

so i open the connection when running these commands and when exitting the application i use the currentproject .openconnection "" .The next time i run the application, it does not prompt for the user name.