• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

SQL 101 question: accessing MSDE over a LAN

Hey, there...

I have two machines connected via an ethernet lan - lets call one HPPAV and another COMPAQ.  HPPAV has a new installation of MSDE 2000, plus the client tools from Microsoft SQL 2000 Standard (e.g., Enterprise Manager, , and a Test database.  I want to enable applications I have running on the COMPAQ (which also has MSDE 2000, as well as SQL 2005 client tools, but probably not pertinent) to have data access (read only, full, whatever) to the Test database on HPPAV.  The HPPAV and COMPAQ do not have common user logon accounts, if that matters.  COMPAQ can access other data easily from HPPAV through the use of shared folders, but I'm having trouble setting this up.

I know this is probably SQL 101, but I haven't had much success in searching over the web - no simple answers, however.

Your help will be greatly rewarded with 500 points!

Kevin
0
kmoloney
Asked:
kmoloney
  • 3
  • 2
1 Solution
 
kmoloneyAuthor Commented:
FYI - To clarify, COMPAQ cannot see the SQL Server on HPPAV.  In Microsoft SQL Server Management Studio (which comes with VB.NET 2005 Professional) I attempt to connect to a server by requesting it to "Browse for more..." under "Server Name:".  HPPAV is not returned.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Try accessing it using IP adress instead of name
0
 
nmcdermaidCommented:
Here is a standard test to test connectivity, do everything in this order and you can work out at what point connectivity drops out (run all of these in a command prompt):


1. PING <IP Address>
    You should get 'reply from ......'


2. PING <Machine Name)
    You should get 'reply from ......'

3. TELNET <machine name> <port>
    <port> is the port that the server is running on. By default this is 1433 but you'll need to verify this by checking the SQL Server logs
    If you get a blank screen then this test is successful
    If you get 'unable to connect' then the SQL Server service is not running, or there is a firewall blocking port 1433

4. OSQL -S <servername> -U <login< -P <password>
   You should get a promtp like this: 1>
   Type exit to exit it.



0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
kmoloneyAuthor Commented:
nmcdermaid,

Before your suggestions, I attempted to logon to the server using an ODBC driver for SQL.  I had made the installation in mixed mode, and was able to access it using the sa username and password - something I'm told isn't the best practice in security.

Steps 1 and 2 went without a hitch; step 3 failed; and step 4 worked.  ???  I am able to connect, now.

Thanks!  It seems to be working; but I think I'm missing the larger concept here.  I opened the firewall at port 1433 and now it looks like I can gain access just fine using a regular SQL driver. But I'm having difficulty understanding the issue of users, permissions, and logons. What would be best practice steps? E.g., how should MSSQLSERVER logon be configured - "Local System Account" or  "This Account" with username and password?  Does the remote computer have to have a user account that matches the client account?  Why could I access the account using an ODBC driver, but not as an SQL server, before I opened up port 1433?  What's the difference between having data access and having a linked server?  So many questions, so few points to award.

I know it sounds like I'm asking for an entire course on SQL here - sorry, but if you have any links that would be helpful, I'd appreciate it.

Kevin

P.S. - you get the points anyway.  Racimo, IP address over server name had nothing to do with the issue.
0
 
nmcdermaidCommented:
If you had a domain controller and many computers then it would be worthwhile setting your service accounts to a user account which has network access.

However you've just got a simple LAN going with no centralised security so you may as well leave the service logon as "Local System Account"

There's one exception though: If you wanted your SQL Server to do something back on the COMPAQ machine then you'd need to run the service under an account which as rights on the COMPAQ machine.

If you had a domain controller / Active Directory (i.e. centralised security database) then you could use a 'domain' account. Since you don't have that you would need to have an account with the same login and password on both machines.


>> Does the remote computer have to have a user account that matches the client account?

If you want to use 'Windows Authentication' to log into SQL Server, and you don't have a domain controller then you need have the same logins between computers.


>> Why could I access the account using an ODBC driver, but not as an SQL server

What so you mean by 'not as an SQL server'??

If can connect as ODBC then you should be able to connect using any other method. They all do the same thing - connect to a service on a computer which runs on a given port (possibly 1433)


>> What's the difference between having data access and having a linked server

"having data access" is a pretty loose term but I guess it means that you can access data. Its a very vague term.

A "linked server" is a specific term within SQL Server where you predefine a connection to a database server or file. Then you can access the link server as a simple logical name within your queries.


>> I had made the installation in mixed mode, and was able to access it using the sa username and password - something I'm told isn't the best practice in security.

No not necesarily. If you're talking about security on a two computer network then it really isn't an issue.

As long as there is a solid sa password its OK, but usually in a system you would connect as a certain user - its certainly is bad practice to set up a system based on the sa user.
0
 
kmoloneyAuthor Commented:
Thanks so much for your very detailed reply.  It's helped a lot!

Kevin
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now