none
MS Access Connection Error to SQL Server 2008

    Question

  • Here is my configuration:

    Windows 2008 Server (newly installed, was on a Windows 2003 server)

         installed with SQL Server 2008 R2 Express Edition

        using Windows Authentication mode

         Firewall is off

    Client: Windows 7, running MS Access adp file using Windows Authentication for connection

    Here is my connection string

    BaseCS = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=LeadTrack;DATA SOURCE=CHDC04\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True"

    CurrentProject.OpenConnection BaseCS

    Here is the error I'm getting

    OBDC SQL Server Driver[DBNETLIB]SQL Server does not exist or access is denied.

    This ms access adp file is working if I'm connected to my old server running Windows2003 and SQL 2000

    Please advise as to what I"m missing. I think it is on the server side, but have checked configuration and security issues.

    Saturday, August 25, 2012 8:31 PM

Answers

  • After many hours of banging my head, got help from a SQL guru that pointed me in the right direction.

    On the server the group that was setup for the users within the database security had to have the schema set to dbo. My support person said because this was a 2000 database being converted to 2008 this had to be set. Don't quite understand how that matters but the application is now working.

    I do now understand a little bit more about Windows Authentication vs SQL Authentication. What a learning experience. I guess I need to learn more about the differences between SQL 2008 and older versions.

    Marnie


    Marnie

    • Marked as answer by MVGSQL Monday, August 27, 2012 7:46 PM
    Monday, August 27, 2012 7:46 PM

All replies

  • The error message means that you are not reaching SQL Server.

    Check:

    1) That the instance has TCP/IP enabled. (By default Express Edition only accepts local connection.) Use SQL Server Configuration Manager.

    2) That the SQL Server Browser Service is running. Again, use SQL Server Configuration Manager.

    I would also recommend that you change the provider to SQLNCLI10. This has nothing to do with the connection problem, but this is a newer provider with full support for all features in SQL 2008.

    You can take out Use Procedure For Prepare - that connection option is only useful with SQL 6.5.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, August 25, 2012 9:29 PM
  • I checked the TCP/IP and it is enabled. I didn't have the SQL Server Browser Service started so I started it.

    I also changed the provider in the connection string to SQLNCLI10 and removed the "use procedure for prepare"

    Now I'm getting "invalid connection string" with Run Time error 6009.

    I also changed the connection string back to the original one and got the same error.

    Does this mean I'm actually reaching SQL Server now?

    Thank you for your quick reply.

    Marnie


    Marnie

    Could it have something to do with my "Data Source" string? On the server the instance name is CHDC04\SQLEXPRESS

    Does this have anything to do with "Remote Connections" to SQL Server. Where do I check if this is set? In SQL Express 2005 Edition there was like this per Microsoft instructions:

    1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then clickSQL Server Surface Area Configuration.
    2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
    3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply

    Don't see this "Surface Area Configuration" option in 2008? Just more guessing

    • Edited by MVGSQL Sunday, August 26, 2012 12:46 AM more information
    Saturday, August 25, 2012 11:59 PM
  • I see now from the error message that you are actually using ODBC, in which case you should not use any OLE DB provider at all. Here is how the connection string should like:

    BaseCS = "Driver={SQL Server Native Client 10.0};Trusted_connection=Yes;Database=LeadTrack;Server=CHDC04\SQLEXPRESS"


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, August 26, 2012 9:03 PM
  • I thought OLEDB was used for ADP connections, not ODBC. Anyway I tried your connection string and got error message

    "Access is not able to perform this operation because the project is not connected to a SQL Server database."

    In your string the driver "SQL Server Native Client 10.0" is that reference to a driver on the server or the local client machine?

    Earlier today I tried my original string after adding Inbound rules to my firewall for TCP/IP port 1433 and UDP port 1434. The firewall is not on, but saw some suggestions on doing this and thought I would try. Now I get the error message

    BaseCS = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=LeadTrack;DATA SOURCE=CHDC04\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True"

    "Can not open database "LeadTrack" requested by the login. Login Failed"

    Now I wonder if this has to do with "Windows Authentication" issues?

    Thanks again for your reply and any assistance in this manner.

    Marnie



    Marnie

    Monday, August 27, 2012 2:22 AM
  • After many hours of banging my head, got help from a SQL guru that pointed me in the right direction.

    On the server the group that was setup for the users within the database security had to have the schema set to dbo. My support person said because this was a 2000 database being converted to 2008 this had to be set. Don't quite understand how that matters but the application is now working.

    I do now understand a little bit more about Windows Authentication vs SQL Authentication. What a learning experience. I guess I need to learn more about the differences between SQL 2008 and older versions.

    Marnie


    Marnie

    • Marked as answer by MVGSQL Monday, August 27, 2012 7:46 PM
    Monday, August 27, 2012 7:46 PM