Re: Connection from MS Access MDB
- From: F W Green <FWGreen@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 17 Dec 2007 11:17:01 -0800
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F32D516E-285B-4F08-B115-8A27F8177350@xxxxxxxxxxxxxxxx
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.
- Follow-Ups:
- Re: Connection from MS Access MDB
- From: Sylvain Lafontaine
- Re: Connection from MS Access MDB
- References:
- Connection from MS Access MDB
- From: F W Green
- Re: Connection from MS Access MDB
- From: Sylvain Lafontaine
- Connection from MS Access MDB
- Prev by Date: Re: Intermittent connection error (with workaround)
- Next by Date: Authentification problem with SQL Server 2000
- Previous by thread: Re: Connection from MS Access MDB
- Next by thread: Re: Connection from MS Access MDB
- Index(es):
Relevant Pages
|
