Unanswered: How To Provide the SQL server Loing dialog data Automatically ?
I am working on an access DB that has linked tables and connects to the SQL by DNS ODBC.
The point is that I want to connect using SQL authentication rather than using windows authentication (to prevent the users to login and link to the tables, just see the provided data on the forms designed to!)
I found a code that creates an odbc by code at the following Link:http://support.microsoft.com/kb/171146/en-us But i am getting SQL server Loing dialog asking for the sa user and password and I cant find any code to provide this automatically.
The following is my code that i was using while login using windows authentication
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim rs2 As Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("ProgV", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Ver", dbOpenSnapshot)
Query1 = "SELECT Machines.SysUserName, Machines.MName From Machines WHERE Machines.SysUserName = fOSUserName() AND Machines.MName = fOSMachineName()"
Set rs = db.OpenRecordset(Query1, dbOpenSnapshot)
the code is not working unless i type the sa user and password "which is not provided to a user"
Do any one have a code to provide the sa username and password automatically
Thanks PatP for your rely,
I have gone through the above Link but i guess i am still confused regarding how to implement this,
I have liked tables that should login to the existing SQL server, and i still don’t find a code to let it through
also i am using SQL 2000 and i am not sure if this is applicable for 2000 or 2005 SQL
do u have any idea how to implement the "sp_setapprole (Transact-SQL)" in to my SQL database and the access application connecting to it ?
Sorry about the delay in responding... I've been a wee bit busy at work lately.
What I would recommend is allowing you users to connect using their existing Windows credentials. This is more secure, better supported, and a whole lot easier to manage than reverting to using SQL Server connectivity in a Microsoft Access application.
The trick that you need is in your application startup macro/module you need to execute the call to sp_setapprole. You give the Windows users access to your database, but NO permissions at all. You grant all of the permissions needed by your application (and only those permissions, no extras) to the role. That way the users connection to the database itself is handled via Windows Authentication, and once the application connects it executes sp_setapprole as the very first thing so that suddenly the application has all of the permissions that it needs.
If the users connect on their own (using MS-Access or other tools), they can connect to the database just fine, but since they have no permissions there is nothing that they can do.
I'm going to move this thread to the MS-Access forum. There are lots of folks there that have the experience needed to ferret out whatever details you need, and explain them better than I can... It has been many years since I've built an application using Microsoft Access for anyone except myself or my family.