locked
Connect To a SQL Database RRS feed

  • Question

  • I know a minute amount of SQL. I was asked to help with some reports by using the queries that were created in an online database and exported the data to Excel. I pull the data into MS Access and create the reports. I would like to work with the SQL queries and the program in general. The primary user has a SQL Server 2012 Express edition on her computer (Windows 7).

    I have the same thing on mine. I was given the database name, login, and password to connect. This doesn't work. I can see connection information on her computer, but cannot get to it. On my computer, I can't even get that part of the program to appear. I do know that in the past, when I set up a SQL database, I can connect to that one. When I log in again, I use the same name and password and I get connected. This one has SQL Authentication.

    Here is my dilemna. That's all I know. I need to connect to the database. The company that supports it couldn't help. The IT Department here could not help. Can anyone help me with this little bit of information I've given?

    Thank you.

    Monday, June 17, 2013 4:49 PM

Answers

  • I am not sure about SQL Server Express 2012 but earlier versions tend to default to "Local Connections" only. 

    The first thing I would do is to go to her PC and use the tool "Surface Area Configuration for Services and Connections" and make sure her SQL Server Express instance has the option "Local and remote connections" (and the sub-option "Using both TCP/IP and named pipes) selected. Stop and re-start the service if you change the option/sub-option to ensure that the instance runs with the new settings.

    You can then go back to your PC and try setting up the connection to her SQL Server Express instance using your SSMS. 

    If you need to create Views / SPs in the database on her SQL Server Express instance, then you will also need appropriate permissions to do so.

    You can also try setting up an ODBC DSN (Data Source Name) accessing the required database on her SQL Server Express instance.  The DSN will be useful if you want to work with Linked Tables (accessing the SQL Server data) in Access.

    Finally, there are "Pass-Through" Queries in Access that can be used to run T-SQL on the database on her SQL Server Express instance also.


    Van Dinh

    • Marked as answer by Fanny Liu Tuesday, June 25, 2013 10:57 AM
    Tuesday, June 18, 2013 3:16 AM

All replies

  • Are you connecting with Excel or MS Access? What version.

    Are you using an ODBC datasource? Perhaps you don't know.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, June 17, 2013 6:02 PM
  • I am trying to connect with SQL Server 2012 Management Studio. Again, all I was given was a name, @@@@@@@@@@@@@.us, a login ID, and a password. I have never connected before. I can log on on her computer and connect with her version. ON mine, it is apparent I need more.
    Monday, June 17, 2013 6:14 PM
  • Start with this short tutorial Tutorial: Getting Started with the Database Engine It covers connecting with SQL Server Management Studio.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, June 17, 2013 7:42 PM
  • I am not sure about SQL Server Express 2012 but earlier versions tend to default to "Local Connections" only. 

    The first thing I would do is to go to her PC and use the tool "Surface Area Configuration for Services and Connections" and make sure her SQL Server Express instance has the option "Local and remote connections" (and the sub-option "Using both TCP/IP and named pipes) selected. Stop and re-start the service if you change the option/sub-option to ensure that the instance runs with the new settings.

    You can then go back to your PC and try setting up the connection to her SQL Server Express instance using your SSMS. 

    If you need to create Views / SPs in the database on her SQL Server Express instance, then you will also need appropriate permissions to do so.

    You can also try setting up an ODBC DSN (Data Source Name) accessing the required database on her SQL Server Express instance.  The DSN will be useful if you want to work with Linked Tables (accessing the SQL Server data) in Access.

    Finally, there are "Pass-Through" Queries in Access that can be used to run T-SQL on the database on her SQL Server Express instance also.


    Van Dinh

    • Marked as answer by Fanny Liu Tuesday, June 25, 2013 10:57 AM
    Tuesday, June 18, 2013 3:16 AM