Can an MS Access, DAO mdb have a connection string to a SQL Server 2005 database?

I have 88 MDBs which CAN access SQL Server 2005 data, using ODBC data sources for the tables.  This works when the network userid is a network Administrator.  However, when others log on to the network as non-adminitrators (as themselves) I get an error from SQL server about not being able to connect.
I'd like to use 'trusted_connection =yes", but can't figure out how to "open" a connection with these DAO mdbs.  Do I have to use ADO?  Can I use both?  Must I rewrite all 88 programs?
If there is a DAO connection string, are there examples available?
bcreenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Well I don't think you need to change the code. The problem is that an administrator is by default also sysadmin on the SQL Server. So he is not having troubles connecting. An ordinary user needs to be granted access.

The security model is a two step rocket. First granted access to the server, then granted access to the databases.

So you'll have to create an AD group, grant this logon rights to the sql server, and then the desired rights in each and every database.

Code example:
-- For the server access
USE [master]
GO
CREATE LOGIN [DOMAIN\ADGROUP] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

-- For each and every database
USE [DBNAME]
GO
CREATE USER [DOMAIN\ADGROUP] FOR LOGIN [DOMAIN\ADGROUP] WITH DEFAULT_SCHEMA=[DBO]
GO
USE [KFM_FE]
GO
EXEC sp_addrolemember N'db_owner', N'DOMAIN\ADGROUP'
GO

There are different roles, for example db_reader or db_writer. There is also the public role wich any user is automatically a member of, you can do it the other way around and grant rights in the databases to public. But I prefere this method. The DEFAULT_SCHEMA clause is also something one might play with. But this is only applicable when a skilled DBA/DBE is involved in the DB Creation process. You can surely use my example as is.

//Marten
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Sorry, sloppy coding:

USE [DBNAME]
GO
CREATE USER [DOMAIN\ADGROUP] FOR LOGIN [DOMAIN\ADGROUP] WITH DEFAULT_SCHEMA=[DBO]
GO
USE [KFM_FE]
GO
EXEC sp_addrolemember N'db_owner', N'DOMAIN\ADGROUP'
GO

should be:
USE [DBNAME]
GO
CREATE USER [DOMAIN\ADGROUP] FOR LOGIN [DOMAIN\ADGROUP] WITH DEFAULT_SCHEMA=[DBO]
GO
USE [DBNAME]
GO
EXEC sp_addrolemember N'db_owner', N'DOMAIN\ADGROUP'
GO

This is [DBNAME] all the way, for each and every database (replace [DBNAME] with real database name ofcourse)

//Marten
0
bcreenAuthor Commented:
Marten, thanks.  Perhaps I also need to give more info.  The databases and tables within each database resulted from converting an accounting package from Sage software.  The old version (called Pro 6.5) had files in FoxPro tables (.DBFs) in a database 'container' (.dbc).  That accounting package already had say, 50 users with user ids and passwords stored within a table (not network userids/passwords).
The conversion from FoxPro to SQL Server 'setup' those 50 or so userids and gave them rights to the various databases.  So all users can open and update the SQL Server tables, etc, VIA the Sage accounting package. (But cannot, yet, with my MS Access applications.) There is a ProSys database with System-type tables and then one table for each of 12 or so divisions or companies within the accounting system.

Perhaps I should try to see if Sage software can hook me up with someone that has done MS Access work AGAINST their SQL Server version of the accounting system -- or perhaps someone 'in house' at Sage has actually tried this.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Hi there

I'm not familiare with the Sage application. But I wonder if it's residing in it's own server/servers.

It's not good coding practice, but a common design nevertheless to let an application server hook up with some application account, and then have some table within the database that checks if youre a valid user. This design 'hotwires' the SQL secrity design. This design would still give you the described scenario, with access for administrators, but not others.

Try my code on the server, and one of the databases. Then you'll know if this is the solution. You can skip the AD Group design, and use a user login instead when testing.

Code to remove/undo is:
-- For each and every database you tried it on
USE [DBNAME]
GO
DROP USER [DOMAIN\ADGROUP]
GO

-- For the server access
USE [master]
GO
DROP LOGIN [DOMAIN\ADGROUP]
GO

(as metioned, DOMAIN\ADGROUP may very well be substituted for DOMAIN\USER)

//Marten
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.