SQL Server 2000 Developer remote access

[hyjacked]

Hey all,

I've been having connecting from a win2k box and a mac box to SQL Server2000 Developer running on winXP using DSN.

So far I haven't been able to figure out the DSN settings to get it to connect. I keep getting cannot find server error messages.

If it is possible, what should the server address be? (ip/path/combination) Also on the next screen what should be done in the client config dialog?

Thanks,
Brad

 

[hyjacked]

sorry about the bad grammar, I knew I should have proof read it better than I did.

So here's the point form version:
-can't connect to SQL Server2000 developer from another machine.
-can telnet to machine that SQL Server is installed on and on the SQL Server port (1652 for some odd reason on my computer)

Does the developer edition support connections from other computers? If so could you give me some info on what the address should be from the other computer to the one with SQL Server. Also I have to do it through DSN. As this is how the client program needs to be setup.

Thanks.

 

[SQLSister]

Do you have service pack 3 loaded of WinXP? If so it may be blocking the port by default.

Questions about posting. See faq183-874

 

[SQLBill]

That should be SP2 as XP SP3 isn't out yet But that shouldn't be an issue since you say the SQL Server is using port 1652 (is that TCP or UDP)?

Do you have permissions set on SQL Server? If you don't then you won't be able to set up a connection. If you do, what kind of authentication is your login set to use for SQL Server?

You should be able to set up the connection using the IP address.

-SQLBill

 

[SQLSister]

Oops, I can;t type. Sorry I did indeed mean SP2.

Questions about posting. See faq183-874

 

[hyjacked]

I installed it myself and used the steps I had used in the past. I had to have it installed such that I could test VB.NET apps.

mixed mode is the auth type(i believe that's what it's called), and i've been trying to log in with sa.

I turned the firewall completely off so the port shouldn't be blocked, also I can telnet into the computer on that port(1652), which I used fport to determine that that was the port SQL Server was listening on. It's tcp, not udp.

So just the ip should be enough? I dont' have to specify the computer/sql server name? (computer_name/SQL_Server_Instance) Is there any extra "formatting" I should do in order to get it to work? adding \\ before the ip or computer name?

 

[SQLBill]

In your ODBC connection, which DSN connection type are you using? File, System or User? (I always use System DSN).

When you set it up, the first screen you give the connection a name, description and the server identification. For the Server information, I use the IP address...nothing else.

Next screen....select the With SQL Server authentication, check the "Connect to SQL Server to obtain default...." and enter the login and password. Click on "Client Configuration". Make sure TCP/IP is checked. Make sure the IP address is in Server Alias and Server Name blocks. In the Port section, uncheck the "Dynamically determine Port" and enter your port number.

Click OK, then next....did that work or did you get an error message?

If you get an error message, can you log directly onto SQL Server? If so, open the SQL Server Error Logs and see if there's any errors.

In Enterprise Manager, right click on the Instance Name and select Properties. Go to the Security tab. What authentication is the instance set for? It needs to be mixed. Also, on the Security tab, what is your Audit level set for? Are you auditing Failure (Failed logins)? If so, see if there's failed logins in the SQL Server Error Log. If there aren't then you aren't getting past the operating system. Look in the Windows Event Viewer logs for related errors.

-SQLBill

 

[hyjacked]

When trying to setup the DSN driver as you suggested, my client config. dialog box was slightly different for TCP/IP. Where you mentioned "Server Name blocks" I assumed it would be the same as "Computer Name" since that is the only other field besides port number.

Having filled out everything, I get the following error message box:
Title: Microsoft SQL Server Login
Connection failed:
SQLState: 'HYT00'
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]Timeout expired

Logging into the SQL Server through enterprise manager, I have the following:
For authentication: SQL Server and Windows
Audit level: None

I changed Audit level to Failure. I'm not seeing any error reports. I've tried watching it through the Profiler app and nothing was showing up there either.

I'm not seeing anything showing up in the Event Viewer..which is leading me to think it has something to do with the DSN setup.

Thanks for the help so far.

 

[SQLBill]

Since you aren't seeing failures in the SQL Server log, that means you aren't getting past the Windows authentication. Are there any related errors in the Windows Event Viewer logs? Also, it looks like your system is timing out passing the authentication data. I remember somewhere there is a way to change the ODBC timeout, but I've never done it and don't know how to do that.

-SQLBill

 

[hyjacked]

I wasn't seeing anything in the Event Viewer Logs related to SQL Server when I checked them.

It strikes me odd that when telnet'ing to the server, it takes next to no time, yet it would time out when trying to connect with the DSN driver. I'm still leaning towards it being the DSN connect string that I would need to modify in order to see the server.

Have you ever used SQL Server 2000 Developer? It was mentioned to me that it might not be possible to even connect from remote computers. I am under the impression that it should be able to be connected to since it's suppose to be the same as the enterprise version. The only difference being that you are not allowed to use the Developer version in production situations, as this would break the license agreement.

Thanks

 

[hyjacked]

Thanks for the input people. Turns out the computer was at fault, trying to connect to SQL Server 2000 Developer installed on another computer works fine.

Thanks again.