1 Replies - 7138 Views - Last Post: 08 August 2012 - 01:09 PM

#1 solarissf   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 27-March 12

login into sqlexpress 2008r2 local database from another pc

Posted 02 August 2012 - 10:50 AM

Good Morning All,

I have been researching this for awhile now and maybe someone can point out what I am missing. First off I have created a local database with name SQL Server (SQLEXPRESS), with a couple of tables.

My goal is for another computer on the network to access this database.
So far I have taken the following steps.

1. From the remote computer, I have mapped a drive to source of the database on my main (host) pc. Z:\MSSQL\DATA. Now, from the remote computer I can physically see the database tempDB.mdf in the folder.
2. On the host computer, in configuration manager, I have enabled TCP/IP, and restarted service.
3. On host computer, logged into database using sql server management studio, I have made sure the checkbox is checked for ALLOW REMOTE CONNECTIONS TO THE SERVER, and SERVER AUTHENTICATION = SQL SERVER AND WINDOWS AUTHENTICATION MODE
4. On host, I went into TCP/IP settings and added TCP PORT to 1433. (dont know why, but I did this because I saw it online)
5. on remote I have SQL server browser RUNNING in automatic mode, log on as LOCALSYSTEM

Now on remote machine I launch SQL SERVER MGTM STUDIO, and no matter what options I choose I cannot log into database

please help

Is This A Good Question/Topic? 0
  • +

Replies To: login into sqlexpress 2008r2 local database from another pc

#2 BBeck   User is offline

  • Here to help.
  • member icon

Reputation: 792
  • View blog
  • Posts: 1,886
  • Joined: 24-April 12

Re: login into sqlexpress 2008r2 local database from another pc

Posted 08 August 2012 - 01:09 PM

I'm really best at troubleshooting this sort of thing in person, but I'll take a crack at it. You may already have figured it out anyway, as your post is almost a week old, and I just read it.

Anyway, Express is "probably" a named instance of SQL Server, and they often don't really tell you how to connect to a named instance. Honestly, I haven't had to connect to a named instance in a couple of years because all our servers are Enterprise edition with a dedicated box. And the only versions of Express I use I access locally with either a dot or the word local.

I just tested it on my own machine. I was able to access that SQL Server with ".\SQLEXPRESS" for the server name locally. I was also able to connect to it by replacing the dot with the name of the server (my local machine). That's probably what you will have to do remotely. It's "ServerName\InstanceName".

It's a shame they don't explain this better to people. I remember being really confused about it when I was new to SQL Server.

No one needs remote access to the .MDF file, ever. Really, you should never provide remote access to anyone except yourself, and then only when you are moving it between boxes.

Generally, you are going to use TCP/IP through port 1433 for communications with the remote database using some sort of driver, such as ODBC. If there is more than one instance of SQL Server on the box, only one of them can use 1433 and the other will have a non-standard port that you will have to specify.

I've never seen anyone use the SQL Server Browser at any of the Fortune 500 companies that I've worked with their SQL Servers.

I "think" SQL Server Browser kind of "announces" a server on the network. But most of the networks I work on are so large that you don't want 500 SQL Servers all announcing themselves. Honestly, I'm not even certain exactly what it does and would not hesitate to say that in a job interview. (Although a better plan might be to learn what it does before my next job interview. But my point is that I've seen a lot of Enterprise SQL Servers where no one was using it.)

Anyway, if it's not just a simple problem of needing to give the instance name when you log on, then I would start troubleshooting as follows (You can get a SQL Server's instance name by looking at it's service under "Services".)

First, make sure the SQL Server is running. There are a couple ways to do this, but if it's service is running under Services you are good.

You might try and verify that it's listening on port 1433. There are a couple of ways to do this. But the first test for this might be to use Telnet at the Windows DOS prompt (command window). Telnet into the SQL Server. The command is something like "TELNET MyServer 1433". You won't get very far. I saw some article on SQL Server's protocol and possibly how to get SQL Server to talk to you. You would probably need a VERY advanced Telnet client that allows you to script things to have any hope of getting SQL Server to respond. But SQL Server will "pick up the phone". If it's listening on 1433, you will get just a blank flashing cursor as if it's waiting for your communication in Telnet. If it's not listening on 1433 it will just error out and refuse the connection (assuming some other service hasn't captured 1433, which is unlikely but possible). This is a test you can perform remotely to make sure you have connectivity between the remote machine and SQL Server. The only thing that can really mess this test up is a firewall in between if you use the server's IP address rather than the host name in your Telnet command.

So, once you've gotten this far successfully. Take it a step further by going into Control Panel, System, Administrative Tools, Data Sources (ODBC) and setup an ODBC DSN to the database whether you need this DSN or not.

Go to the System DSN tab and hit the Add button. Scroll all the way to the bottom and select SQL Server (or SQL Server Native Client might be a better choice with a slightly different setup here).

The name in ODBC is just whatever name your programs are going to call this connection by. Fill in the server with your server name, and I'm sure you'll have to give the instance name for a named instance.

Do Next through the rest of the screens selecting either Windows Authentication or SQL Authentication (if you do not have a Domain Server on your network). Select your Default Database. At the end it will give you the oppurtunity to test the connection and should tell you the test went ok.

Of course, if you have Management Studio on the remote machine you can use that to test connectivity. But programs will use some sort of driver such as ODBC, OLE DB, ADO, etc.

This post has been edited by BBeck: 08 August 2012 - 01:10 PM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1