June 20, 2008 at 4:09 pm
I have MS SQL Server 2005 Express Edition installed on windows XP sp2.
Here is the situation:
I am working with a user that has a MS SQL Server 2005 installation with a Microsoft Access front-end.
I want to replicate his environment on my machine, so here is what i did.
1. Backed up the users database to .bak file.
2. Restore the database on my MS SQL 2005 Server Express Edition. I can see all the tables and data for the restored database. The database login is in Windows Authentication Mode.
3. Ran the Microsoft Access front-end (MDB file).
4. Popup comes up saying it can not connect to ms sql server and asks then it asks for the server name and user to login into the database since it can not connect to the pre-defined credentials in the MDB file.
I put in the name of the server running on my machine and i used a trusted connection with the windows authentication user I defined. I can connect and view everything in the database with this user in MS SQL Management Studio Express.
5. I get an error popup that says:
Connection failed:
SQLState: '08004'
SQL Server Error: 4060
Server rejected the connection; Access to selected database has been denied
I have added the user in question to the user list for the database and also have added the db_owner role to it.
This is where I am stuck. Any help is appreciated. Thank you.
June 20, 2008 at 4:42 pm
Check to make sure that the user has access to whatever his default database is, or make sure the database you want him to access is his default. That's the usual culprit in these cases.
June 21, 2008 at 10:15 am
Is the user's login mapped correctly as well?
June 23, 2008 at 10:02 am
The user has the required database set to default.
The user mapping includes the required database.
I have listed links to screenshots for both, take a look. Any help is great, i am in a tight spot.
http://www.uploadit.org/my.php?image=http://server2.uploadit.org/files/tehprow-default_db.jpg
http://www.uploadit.org/my.php?image=http://server3.uploadit.org/files/tehprow-user_mapping.jpg
June 23, 2008 at 10:12 am
When you set up SQL Server express, did you enable the protcol you're using to connect (TCP, Named Pipes, etc) using the surface configuration tool?
June 23, 2008 at 10:33 am
I set it to local connections only. I am running the database and MS access application on the same machine under the same user (me).
June 23, 2008 at 10:41 am
I set it to include tcp and pipe connections but i get the same error.
June 23, 2008 at 10:47 am
Just for testing purposes - turn off your Windows Firewall, and try that again. Make suer that SQL Browser is running as well.
Also - can you connect through the ODBC tools? Like - create a DSN to the SQL Server?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 23, 2008 at 10:50 am
It might also help to post the connection string that the Access front end is using.
June 23, 2008 at 10:53 am
Where can i find the ODBC tools? I am a newbie so i require a little bit more details.
June 23, 2008 at 10:54 am
Dim dbldbl As Database
Dim rslRs, rslRs1 As Recordset
Dim sglcriteria, sglcriteria1 As String
Set dbldbl = DBEngine.Workspaces(0).Databases(0)
Set rslRs1 = dbldbl.OpenRecordset("TUserCode", DB_OPEN_DYNASET)
That is what it looks like. I can not seem to find the details of the connection, can you tell me where I can find that in ACCESS ? What does DBEngine.Workspaces(0).Databases(0) refer to?
I searched in form in the vbeditor and all i could fine was the above string which does the db work.
June 23, 2008 at 10:54 am
Control Panel, Administrative tools, data sources (ODBC).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 24, 2008 at 10:53 am
I am looking for the connection string in the MS Access VBeditor. All i can see that looks like some sort of database connection is:
DBEngine.Workspaces(0).Databases(0)
where is the actual credentials for the connection such as user, database, password, and so on...
Thank you.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy
