none
SQL Server 2005 Express Edition & ACCESS 2007 : how to solve '08004' & '4060' error

    Question

  • I've written a small MS Access 2007 program having links with the tables of SQL Server 2005 Express Edition. The problem is it worked out in development environment. But it's giving error during production environment.

    SQL State '08004'

    Sql Server Error: '4060'

    Server rejected the connection; Access to selected database has been denied.

    How to fix this error?

     

     


    nories
    Friday, April 02, 2010 6:34 PM

Answers

  • If they are using the SQL Express system by default it installs with the SQL Server Accounts Disabled and they only allow Trusted connections, Also the remote connections are disabled. As the sa account is a sql server account this would be disablled.
    http://www.virtualrealm.com.au - XNA Game Programming News and Resources from Downunder.
    Monday, April 05, 2010 6:12 AM

All replies

  • When developing you probably created the database so you would have ownership over it. When pushing it out the users that are connected may not have permissions to the objects in the database. I would start up the management studio on the sqlexpress machine and verify the permissions for the users that need to connect.

    If these users are on a domain it might be easier to set up a domain security group for the users and then add that group to the database.


    http://www.virtualrealm.com.au - XNA Game Programming News and Resources from Downunder.
    Saturday, April 03, 2010 10:30 PM
  • Thanks for your reply wilson, I should have elaborated before itself. Actually I've used ODBC to link SQL Server table.  In development PC I've a instance 'IS\SQL2005'. The user I chose is 'sa' After developing I've packaged for deployment in MS-ACCESS 2007.

    When it is installed in client( This PC works in a remote area and no connection with DOMAIN) I'm getting the above error. I think By default 'sa' has full rights and it should work right?

    Please correct me if I'm wrong.

     

     

     


    nories
    Sunday, April 04, 2010 5:01 AM
  • If they are using the SQL Express system by default it installs with the SQL Server Accounts Disabled and they only allow Trusted connections, Also the remote connections are disabled. As the sa account is a sql server account this would be disablled.
    http://www.virtualrealm.com.au - XNA Game Programming News and Resources from Downunder.
    Monday, April 05, 2010 6:12 AM
  • I am getting the same error when connecting from Access 2007 to SQL Express 2008. SQL Express was installed with mixed mode, and I can connect as SA when running odbcad32 (which I think proves that SQL accounts were enabled when SQL was installed). I have the SQL Native Client 10.0 installed. Both Access and SQL Express are running on my local pc while I am developing.

    When I try to connect via access using the same ODBC driver and SA credentials which work in ODBCAD32 I get the above error codes. In SQL Configuration Manager I have enabled TCP/IP. I guess I have missed a setting somewhere to allow Acces 2007 to connect. Can you tell me where to find the option which needs to be enabled please?

    Friday, April 30, 2010 9:57 AM