Walk me through configuring a db for trusted authentication with SQL Server 2005?

[paro]

I am using SQL Server 2005 Express and the Management Studio as the backend
for an Access 2003 database. I would like to use trusted authentication. So
far have done this:

Created a windows group called dbusers
Added some windows users to this group
Created a SQL Server user called mydbuser
Created a SQL Server login called workstationname\dbusers, with windows auth,
and default db pointed at correct db, no sever roles, users mapped is set to
the correct db, the mydbuser user, with data reader and data writer roles on.
In the Securables dialog I've granted perms to some tables and the specific
database.
Created a system dsn for the database with trusted connection, and it tests ok.

But when I want to link tables from this odbc source when logged in as a
dbusers member, I only see system items like INFORMATION.SCHEMA.blahblah and
dbo.spt_system_fallback. I have been over and over these steps but I'm missing
something basic. Why are the tables I have created in SQL Server not available
for linking?

Sorry for posting in three newgroups, it's not for an adp, but people there
might have a clue re my issues, and the odbc newsgroup is rather
sparse...formscoding is the hotspot, between the three I hope to be able to
connect to someone who can see my error.

 

[Sylvain Lafontaine]

To see all the tables for linking purposes, I suppose that maybe the user
must be a member of the dbowner role for the database. You have given his
role data reader and data writer permissions but I'm not sure if this is
sufficient in the context of creating ODBC linked tables.

Second, I'm confused by the fact that you have created both one or more
windows accounts and a SQL-Server account mydbuser. You don't say also what
you have done exactly with the mapping workstationname\dbusers: setting its
default db to point to the correct db doesn't give it any right to use this
db and giving rights to the mydbuser account won't give any rights to
windows accounts.

As a start, you should try with either a Windows account or a SQL-Server
account and not both at the same thing (and get confused between these two
groups by the same occasion).

As a second suggestion, you should start with the easy things first and then
go for the complicated things later. First, try with the sa account, if
possible. Second, create a sql server account with a login and a password,
make it the owner (dbo) of the database and try it. Third, use your
mydbuser windows account but first, make it a member of the dbowner role.
After that, you can try with more restricted options to see where the stuff
falls apart.

For SQL-2000, each user who is part of the dbowner role is also considered
the owner of the database and the dbo alias will be used, even if this user
account is not dbo. For SQL-2005, things are a little bit different but you
shouldn't see any difference when creating an ODBC linked table. Of course,
it might be a good idea to not use the advanced schema capabilities of
SQL-2005 and keep things simple at the beginning.

 

[paro]

To see all the tables for linking purposes, I suppose that maybe the user
must be a member of the dbowner role for the database. You have given his
role data reader and data writer permissions but I'm not sure if this is
sufficient in the context of creating ODBC linked tables.

Second, I'm confused by the fact that you have created both one or more
windows accounts and a SQL-Server account mydbuser. You don't say also what
you have done exactly with the mapping workstationname\dbusers: setting its
default db to point to the correct db doesn't give it any right to use this
db and giving rights to the mydbuser account won't give any rights to
windows accounts.

As a start, you should try with either a Windows account or a SQL-Server
account and not both at the same thing (and get confused between these two
groups by the same occasion).

As a second suggestion, you should start with the easy things first and then
go for the complicated things later. First, try with the sa account, if
possible. Second, create a sql server account with a login and a password,
make it the owner (dbo) of the database and try it. Third, use your
mydbuser windows account but first, make it a member of the dbowner role.
After that, you can try with more restricted options to see where the stuff
falls apart.

For SQL-2000, each user who is part of the dbowner role is also considered
the owner of the database and the dbo alias will be used, even if this user
account is not dbo. For SQL-2005, things are a little bit different but you
shouldn't see any difference when creating an ODBC linked table. Of course,
it might be a good idea to not use the advanced schema capabilities of
SQL-2005 and keep things simple at the beginning.

I am sure I twisted too many knobs trying to get connected. I would like to
start at the simplest possible level and then take it from there.

A major clue was the dbowner role that you mentioned. It allowed access to the
app tables and even to run sprocs.

But since I had likely created an overly complex user/login config I tore all
of it down to see what the simplest case would be to allow connection and
access to the data. I had somehow thought (because of the map user to db
dialog) that I needed to create a user as well as a login.

I'll be hacking away at this but it'd be great to hear from you exactly what
steps should be taken to allow a windows group (like dbusers) to connect and
access a specific database. I think something like: create the windows group,
add members, create sql login for that group, map to the db they should have
access to, set role dbowner. That's not enough to do it, what else is needed?

 

[paro]

As mentioned I started from scratch. I have a new problem...

Create the windows group dbusers, add members
In SQL Server 2005 M Studio
Create sql login for that group
In User Mapping, map to the db they should have access to, set role dbowner.
In the database, open the securables dialog for the user. Just for tesing
permit everything for the database and one of the tables.

Back in the user login, don't see any tables from the db for linking. I've
tried the above with some variations five times and the first time I rebuilt
it all, I was able to link to tables. Since then, no luck. I'm not sure what
was different.

Curiously, when I click on the Effective Permissions button in the securables
dialog (either for user or login) an error is reported "Cannot show requested
dialog. Cannot execute as the server principal because the principal blahblah
does not exit, this type of principal cannot be impersonated, or you do not
have permission SQL error 15406". I'm attempting this as an admin on the
server. I don't know if this is related to the non-availability of the tables
for relinking, as I never tried the Effective Permissions button till I was on
one of these non working configs.

This is sure a time killer.

 

[Sylvain Lafontaine]

Maybe you have a problem with the default schema in the mapping. Set it to
blank or even better, set it to dbo . The schema thing can be more
difficult to use in SQL-Server 2005 than in SQl-2000.

Also, as I've already suggested, you should start by using a SQL-Server
login account instead of a Windows account and your very first step should
be to test with the sa account.

By default, assigning the db_owner role should be more than sufficient for
any user account. By default, the sa account as a server wide role of
sysadmin, so it should also be sufficient by itself.

In your case, make sure that you have not used anything else than dbo as the
schema of all your tables when you have upsized them. Using anything else
might hide the tables from ODBC (not necessarily but as you are already in
trouble, no need to complicate things further).

 

[paro]

Maybe you have a problem with the default schema in the mapping. Set it to
blank or even better, set it to dbo . The schema thing can be more
difficult to use in SQL-Server 2005 than in SQl-2000.

Also, as I've already suggested, you should start by using a SQL-Server
login account instead of a Windows account and your very first step should
be to test with the sa account.

By default, assigning the db_owner role should be more than sufficient for
any user account. By default, the sa account as a server wide role of
sysadmin, so it should also be sufficient by itself.

In your case, make sure that you have not used anything else than dbo as the
schema of all your tables when you have upsized them. Using anything else
might hide the tables from ODBC (not necessarily but as you are already in
trouble, no need to complicate things further).

It was a good clue to start with sa and a sql login, because then I saw that
the objects were still just system objects, and of course sa should be able
to. I'd not set the default db to the db that needed to be opened. So now all
seems ok tho I still don't know why I was getting that error on effective
permission. I had started ignoring the default db early in this I
misinterpreted something to mean that it didn't matter...where the intent of
the advice was that setting the default db wasn't enough to grant access to
the db.

Thanks for your patience, you helped me a great deal.