Connection from MS Access MDB

This is a cross-post (I originally posted in the ODBC forum).
I have an Access MDB with linked tables to a database that is running in 2005
SQLExpress.
We created a new instance of SQLExpress and loaded the database from a
backup. We did a full restore of the d/b onto the new instance.
The new SQLExpress instance is using dynamic ports versus the static
(standard) port of 1433 that the old instance of SQLExpress was set to.
The MDB is used for some reporting. The linking of tables and production
of the reports works if run by a user with domain level administrator
permissions.
In the old instance of SQLExpress, a regular domain user would fail and the
pop-up window would appear allowing a change of credentials to produce the
report. In the new instance, this does not appear; only messages of an
exception occurring or if I run the report from the MDB Report section,
versus the menu that was built, I get an ODBC error, generic 3146 message
about being on a network.
I have used sql tracing from the ODBC Data Sources and I see (for both
regular and admin users) the attempted sql connections with the "admin"
account from the MDB and the domain user id; both fail; both are recorded in
the server Event Log.
In the case of a domain admin user running the reports, the log continues
and shows successful access to the database. In looking at the server Event
Log, the success connection is shown as a 'trusted' connection.
Is there a setting within SQLExpress for the server or database to cause the
pop up window to appear and allow the changing of credentials? I have tried
to create an account within the MDB to match an account within the database
and the server but I can not get the MDB to use that account; it always
defaults to the 'admin' account.
Thank you.
Which ODBC forum? There are many of them.
It's not clear from your description if you want the credential window to
appear each time or not or if you want to use a domain (or windows or
"trusted") or a standard (or sql-server) user account.
If you are using a standard sql-server account, make sure that the
SQL-Server is setup for mixed authentification (Windows + SQL-Server
account) because only windows accounts are allowed by default. For the
standard accounts themselves, if you are trying to use accounts that were
created before the restoration, make sure that they are correctly mapped to
their SID by using the sp_change_users_login procedure (or better yet:
delete and recreate them). See
http://msdn2.microsoft.com/en-us/library/ms174378.aspx .
If you want to use integrated security, make sure that the accounts that you
want to use are mapped as logins on the SQL-Server: the fact that an account
can log on a windows server doesn't mean that it can log on the sql-server
itself.
If you still have problem, then delete all links and recreate them using
either a standard sql-server account (account + password) or a windows
account (ie., integrated security or "trusted" account). If you want the
mdb file to retain the password, then check the option "Save Password" when
(re-)creating the links.
If these links are created programmatically (using vba code), then don't
forget to use the attributes DB_ATTACHSAVEPWD if you want the new links to
keep the password. Of course, you don't have to use this attribute for
windows accounts. See http://www.accessmvp.com/djsteele/DSNLessLinks.html .
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
news:F32D516E-285B-4F08-B115-8A27F8177350@.microsoft.com...
> This is a cross-post (I originally posted in the ODBC forum).
> I have an Access MDB with linked tables to a database that is running in
> 2005
> SQLExpress.
> We created a new instance of SQLExpress and loaded the database from a
> backup. We did a full restore of the d/b onto the new instance.
> The new SQLExpress instance is using dynamic ports versus the static
> (standard) port of 1433 that the old instance of SQLExpress was set to.
> The MDB is used for some reporting. The linking of tables and production
> of the reports works if run by a user with domain level administrator
> permissions.
> In the old instance of SQLExpress, a regular domain user would fail and
> the
> pop-up window would appear allowing a change of credentials to produce the
> report. In the new instance, this does not appear; only messages of an
> exception occurring or if I run the report from the MDB Report section,
> versus the menu that was built, I get an ODBC error, generic 3146 message
> about being on a network.
> I have used sql tracing from the ODBC Data Sources and I see (for both
> regular and admin users) the attempted sql connections with the "admin"
> account from the MDB and the domain user id; both fail; both are recorded
> in
> the server Event Log.
> In the case of a domain admin user running the reports, the log continues
> and shows successful access to the database. In looking at the server
> Event
> Log, the success connection is shown as a 'trusted' connection.
> Is there a setting within SQLExpress for the server or database to cause
> the
> pop up window to appear and allow the changing of credentials? I have
> tried
> to create an account within the MDB to match an account within the
> database
> and the server but I can not get the MDB to use that account; it always
> defaults to the 'admin' account.
> Thank you.
>
|||Sylvain:
Thank you for this information; it is putting me in the correct direction.
Some comments and more questions:
1. I posted in the SQL Server Open Database Connectivity (ODBC) forum first
but saw similar answer of yours here; that is why I reposted.
2. I am set up for mixed authentication and I would prefer to have the
credential window appear each time so that the generic, read-only, account is
used to generate the report. If I could set the MDB to use the generic
account without the credential window, that would be good but that might
prevent administrative debugging or changing of the MDB without a relink of
tables.
3. I believe that I understand the sp_change_users_login procedure but I
have not done that yet. I did a REPORT and it shows 2 accounts are not
linked but there is another account that "must be" linked as it did not
appear in the report. I may still do the change on the account that appears
to be linked to ensure that it is linked. The accounts, the one not
reported, were created in the d/b and server AFTER the restore; so the change
may be needed.
4. I am not sure how to get the MDB to retain the password as you
mentioned. I created a DSN for the linking of the MDB to the database. I
used the SA account for the linking process because any other account failed.
Did I miss something here?
5. Unfortunately, we do not have in-house VBA expertise to use the
DSNlesslink that you mentioned; that makes me a little reluctant to go that
route.
F W Green
"Sylvain Lafontaine" wrote:

> Which ODBC forum? There are many of them.
> It's not clear from your description if you want the credential window to
> appear each time or not or if you want to use a domain (or windows or
> "trusted") or a standard (or sql-server) user account.
> If you are using a standard sql-server account, make sure that the
> SQL-Server is setup for mixed authentification (Windows + SQL-Server
> account) because only windows accounts are allowed by default. For the
> standard accounts themselves, if you are trying to use accounts that were
> created before the restoration, make sure that they are correctly mapped to
> their SID by using the sp_change_users_login procedure (or better yet:
> delete and recreate them). See
> http://msdn2.microsoft.com/en-us/library/ms174378.aspx .
> If you want to use integrated security, make sure that the accounts that you
> want to use are mapped as logins on the SQL-Server: the fact that an account
> can log on a windows server doesn't mean that it can log on the sql-server
> itself.
> If you still have problem, then delete all links and recreate them using
> either a standard sql-server account (account + password) or a windows
> account (ie., integrated security or "trusted" account). If you want the
> mdb file to retain the password, then check the option "Save Password" when
> (re-)creating the links.
> If these links are created programmatically (using vba code), then don't
> forget to use the attributes DB_ATTACHSAVEPWD if you want the new links to
> keep the password. Of course, you don't have to use this attribute for
> windows accounts. See http://www.accessmvp.com/djsteele/DSNLessLinks.html .
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
> news:F32D516E-285B-4F08-B115-8A27F8177350@.microsoft.com...
>
>
|||First, sorry for the late response.
Second, I haven't used MDB with ODBC linked tables since many years, so I
forgot nearly everything about all these little naughty details; so you will
have to make your own little tests in order to see what works and what
don't.
The sp_change_users_login procedure is only for accounts that were created
before the restoration and only in the case when the master database has not
been restored or is from another installation/instance. Not usefull for new
accounts. Also, you see that the use of roles is a better idea than to
directly assign permission to an user account because with roles, it's
pretty easy and straightforward to recreate the old user accounts and
(re-)associate them with their respective roles. (BTW, I don't remember if
you have to use the sp_change_users_login procedure for these cases.)
For the point 4., some things can change when you are using a DSN instead of
a DSN-less connection but again, it's something that I've forgotten a long
time ago and you will have to make your own tests. I'm surprised however
that only the sa account is working for you. I suppose that you may have
forgot to associate these other accounts to their databases.
In all cases and excerpt maybe for the saving of the credentials - which I
don't remember the details -, both methods (DSN or DSN-less) should work as
well as each other.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
news:9B799CE8-E732-4DF5-8D9C-023C72ABD890@.microsoft.com...[vbcol=seagreen]
> Sylvain:
> Thank you for this information; it is putting me in the correct direction.
> Some comments and more questions:
> 1. I posted in the SQL Server Open Database Connectivity (ODBC) forum
> first
> but saw similar answer of yours here; that is why I reposted.
> 2. I am set up for mixed authentication and I would prefer to have the
> credential window appear each time so that the generic, read-only, account
> is
> used to generate the report. If I could set the MDB to use the generic
> account without the credential window, that would be good but that might
> prevent administrative debugging or changing of the MDB without a relink
> of
> tables.
> 3. I believe that I understand the sp_change_users_login procedure but I
> have not done that yet. I did a REPORT and it shows 2 accounts are not
> linked but there is another account that "must be" linked as it did not
> appear in the report. I may still do the change on the account that
> appears
> to be linked to ensure that it is linked. The accounts, the one not
> reported, were created in the d/b and server AFTER the restore; so the
> change
> may be needed.
> 4. I am not sure how to get the MDB to retain the password as you
> mentioned. I created a DSN for the linking of the MDB to the database. I
> used the SA account for the linking process because any other account
> failed.
> Did I miss something here?
> 5. Unfortunately, we do not have in-house VBA expertise to use the
> DSNlesslink that you mentioned; that makes me a little reluctant to go
> that
> route.
> F W Green
> "Sylvain Lafontaine" wrote:
|||Sylvain:
Thank you very much for your assistance. I believe that I have solved the
issue mostly through trying many things; not because I really know much about
SQL Server.
The SQL server and instance are new; therefore, there is a new master db. I
used SQL Mgmt Studio to restore the d/b from a backup from the old server.
Therefore, the issues of linking between the server and the d/b were real.
No matter what I did, I could not resolve the State 5 error which was an
invalid user. The server logs showed the MDB ADMIN attempted connection and
then my user account without domain preface attempted connection. Both
failed many times when the MDB report was being generated. If a non-Admin
user was running the report, it failed. If a domain Admin user (me) ran the
report, it worked.
In all of my searching, this link,
http://www.webservertalk.com/archive132-2006-10-1710650.html, mentioned 2
things - a) SQLCMD and b) Builtin/Users.
I used the sqlcmd command on the Windows server where the new SQLExpress
instance was running and the logins were okay. Does not prove much but at
least the accounts and passwords to the server were correct.
I checked and in the new instance of SQLExpress, Builtin/Users did NOT have
'dbreader' access to my database. Builtin/Users did not exist in the old
instance but it had been upgraded to SQLExpress from MDSE. In the new
instance, I granted 'dbreader' access to Builtin/Users and now every person
in the firm can run the reports without issue and without the pop-up
authentication window. I am not too concerned about security as the MDB is
strictly a reporting tool and any tweaks will be done by my staff.
Thanks again.
F W Green
"Sylvain Lafontaine" wrote:

> First, sorry for the late response.
> Second, I haven't used MDB with ODBC linked tables since many years, so I
> forgot nearly everything about all these little naughty details; so you will
> have to make your own little tests in order to see what works and what
> don't.
> The sp_change_users_login procedure is only for accounts that were created
> before the restoration and only in the case when the master database has not
> been restored or is from another installation/instance. Not usefull for new
> accounts. Also, you see that the use of roles is a better idea than to
> directly assign permission to an user account because with roles, it's
> pretty easy and straightforward to recreate the old user accounts and
> (re-)associate them with their respective roles. (BTW, I don't remember if
> you have to use the sp_change_users_login procedure for these cases.)
> For the point 4., some things can change when you are using a DSN instead of
> a DSN-less connection but again, it's something that I've forgotten a long
> time ago and you will have to make your own tests. I'm surprised however
> that only the sa account is working for you. I suppose that you may have
> forgot to associate these other accounts to their databases.
> In all cases and excerpt maybe for the saving of the credentials - which I
> don't remember the details -, both methods (DSN or DSN-less) should work as
> well as each other.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
> news:9B799CE8-E732-4DF5-8D9C-023C72ABD890@.microsoft.com...
>
>