none
Cannot Link to a local SQL Server Express Database RRS feed

  • Question

  • Hi,

    I'm trying to reproduce an environment my client has on his computer.  He has an Access 2003 adp file with his code and a SQL Server Express database.  I have both the database and Access code installed on my computer.

    However, the Access adp file will not connect to the SQLServer Express database.  The error message says " Cannot find the database or access is denied".  I cannot even create an ODBC entry using a SQL Server or SQL Server Native Client driver.

    I'm starting to think there is some driver missing in my Windows 7 computer.

    Any ideas?

    Thanks, Alan

    Thursday, November 14, 2019 3:09 PM

All replies

  • Use SQL Server Configuration Manager to verify that the service is started and that the connection protocols you are trying to use are enabled (SQL Express TCP/IP is disabled by default)

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Thursday, November 14, 2019 4:28 PM
  • Hi Kevin.

    I have all 4 protocols enabled.  The SQlExpress engine is running, as well as the SQL Server Browser. 

    I'm not sure which protocol I should be using to connect.  This is a local SQLServer Express installed on the same machine where I'm trying to create the ODBC connection.  Both Named Pipes and TCP/IP failed.  I tried drivers named "SQL Server" and "ODBC Driver 11 for SQL Server".  Nothing works.  It looks like it's just not seeing the database.

    However, SQl Server Management Studio works fine.

    I'm almost ready to give up - been working on this for days.

    Thanks,

    Alan

    Thursday, November 14, 2019 5:20 PM
  • " Cannot find the database or access is denied" is different than "Login Failed"

    Can you connect to master?  Or, verify the permissions of the account you are using?


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Thursday, November 14, 2019 5:31 PM
  • No.  I cannot connect to "master" or any database.  Everything I have tried keeps telling me "SQL server not found" or access denied".

    Insofar as Security goes, I'm not sure what I should be looking for.  I'm using Windows authentication and my Windows login name is listed as a database user and is designated as a db_owner for my target database.

    Again, it's like the SQLServer database is not being seen by the ODBC manager OR by my Access adp application.

    Alan

    Thursday, November 14, 2019 5:43 PM
  • I understand the frustration...sql security is....weird.

    In SSMS, connect to the instance, then Security, then Logins...make sure you are there.

    I'm sure you know, but there is a big difference between Logins and Users (for future readers of this thread)

    Also, are there errors in the SQL Server errorlog, and are they different than what you get from the connection attempts?


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Thursday, November 14, 2019 5:50 PM
  • Hi.

    Yes, the Logins include mine as a db_owner.

    I'm trying to use TCP/IP at this point.  The message that comes back is "Connection Failed".  SQLState='08001'  SQL Server Error:6

    I don't really see any errors in the Log File, as far as I can see.

    Alan

    Thursday, November 14, 2019 6:40 PM
  • db_owner is database level.

    What are you at the instance level?:


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Thursday, November 14, 2019 6:49 PM
  • At instance level:

    Server Roles: public, serveradmin, sysadmin

    User Mapping: db_owner, public

    Permission  to connect: Grant

    Login: Enabled

    Status for SQL Server authenticaion: not checked to lock out login

    Thursday, November 14, 2019 6:59 PM
  • Should SQL Server be running as "Network Service", "Local Service", or "Local System"?  I think I tried all 3 though.

    The instance of SQL Server is on my local system.

    Thursday, November 14, 2019 7:48 PM
  • That should not matter

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Thursday, November 14, 2019 7:58 PM
  • Kevin,

    I want to thank you for trying to help me out.  This issue is very annoying.  If I can't get this working, I may lose a contracting gig.  I may try installing SQL Server Express on another laptop system I have and see if I can get it to work there.  I need to do a bit of prep work on that system first, though.  If you can think of anything else, please let me know.  I may even spring for some remote login assistance.

    Regards,

    Alan

    Thursday, November 14, 2019 8:26 PM
  • Last idea for now:

    https://success.trendmicro.com/solution/1117712-creating-a-data-link-udl-file-and-testing-the-connectivity-to-a-microsoft-sql-server

    This sounds like a name is bad, or something else.  Does not sounds like an Access problem to me.  The UDL above might help.  Or, is the Access connection the only thing that is failing?

    I'm out of these forums until tomorrow.



    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Thursday, November 14, 2019 8:53 PM
  • The Access connection is failing, yes.  However, I also can't create an entry in the Windows ODBC application, for that SQLServer instance.  Actually, that may be why Access is failing.
    Thursday, November 14, 2019 9:26 PM
  • Hi Alan,

    Please refer to this article to solve connectivity errors to SQL Server.

    Solving Connectivity errors to SQL Server

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 15, 2019 6:24 AM
    Moderator
  • However, the Access adp file will not connect to the SQLServer Express database.  The error message says " Cannot find the database or access is denied".  I cannot even create an ODBC entry using a SQL Server or SQL Server Native Client driver.

    What is the exact error message? I have the impression that you post from memory and you did not get the exact wording.

    What does the connection string say?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, November 17, 2019 8:19 PM