PCFormat Forum Archive

Hi

I need your help. I am totally ignorant when it comes to setting up an SQL server. I thought it was this big, dedicated machine that required all sorts of expertise to set it up. Seems I was half right. I put the software on a machine and I have everything working well on that PC. I can link the ODBC to my file dsn and I relinked my tables in Access and everything works perfectly.

Now the problem comes when I try and set up and connect from other machines. I have turned the firewall on the SQL machine off and I have set up SQL to listen to piped, named and tcpip. I can ping the server by ip address and by name. What it won't do is let me connect the ODBC. It gives errors. I googled the errors and did all they said except the telnet thing. Now Jamin mentioned that too and I was wondering how do I do that?

Does the firewall on the individual PCs also block the telnet? I noticed that when doing the ODBC the new native driver for SQL is not there. So I thought I would be clever and get it. The only sites I found want to sell it to me. So I tried using the SQL setup file but the machines have never had updates done and won't run it. My programmer told me to get nDac which I did - which then wanted .net framework 3.5 - we got 4.0. It did not like that so we got 3.5 - it still did not like that.

So overnight I left the machine doing all the necessary updates. I need this to work. Hours of my time are being wasted on what their IT guy should be doing. But he doesn't have the knowledge (and clearly neither do I).

What are the things I need for the client machines to connect happily?
Where can I get this SQL 10 driver?
Do all the firewalls need to be off?
Do all the machines need to be completely updated?
Could an anti-virus program be stopping my data connection?

Plea: I am self taught. Yes the blonde colour goes all the way down past the roots - I need your help urgently. Just look at my title. Any suggestions will be accepted and tried. I do not discriminate. I am getting to the point where I will pay someone to come and fix this for me - just so that pains in my head will go away. My brain was never meant for this (though it doesn't like the kitchen either). Pleeeeeeaase help me........

YOu have an app that installed... It is called SQL Server Configuration Manager. In it you will find a section saying Protocols. You need to enable the protocols you are connecting remotely through. Oh and you need to restart SQL Server afterwards.

You need to be able to connect to the server on port 1433 from all clients. .
To test this, open a command prompt and type telnet sql-server-ip 1433
If the prompt screen goes blank WinRAR, if it says unable to connect, speak to whoever looks after your networks and firewalls

You don't tell us how your clients connect to the SQL server. Is there an app you install on a client that needs an ODBC connection? If so does the connection have to have a specific name, or can you choose from all the created connections on the system from within the app?
Also, an ODBC connection needs to be created on the client side, NOT the server side - as RRF said, SQL Server has it's own connection manager that you client-side ODBC's will connect to.

I know. Working on that now thanks. Will let you know how it goes.

jamin_za wrote:You need to be able to connect to the server on port 1433 from all clients. .
To test this, open a command prompt and type telnet sql-server-ip 1433
If the prompt screen goes blank WinRAR, if it says unable to connect, speak to whoever looks after your networks and firewalls

You don't tell us how your clients connect to the SQL server. Is there an app you install on a client that needs an ODBC connection? If so does the connection have to have a specific name, or can you choose from all the created connections on the system from within the app?
Also, an ODBC connection needs to be created on the client side, NOT the server side - as RRF said, SQL Server has it's own connection manager that you client-side ODBC's will connect to.

Not sure if I understand you here but I use Access to connect and to link to the Sql tables I need an ODBC connection. This we create on each individual machine.

Ha ha the telnet thing didn't work. I have asked them to make it accessible. Thanks I could hug you

How do they open the port?

Tribble wrote:How do they open the port?

==

RuadRauFlessa wrote:YOu have an app that installed... It is called SQL Server Configuration Manager. In it you will find a section saying Protocols. You need to enable the protocols you are connecting remotely through. Oh and you need to restart SQL Server afterwards.

I have done that long ago. Even created an alias using only tcpip. The company that managers the network cannot telnet either. This is so frustrating

Ok we are about ready to pay someone to fix this up for us. Can you recommend anyone? And no - I will not pay for airfares but I will buy them lunch.

I found this

On the server open a cmd prompt and run svrnetcn
Check all the SQL instances and the protocols they are using (if nothing is
shown under enabled protocols you'll have to add something)

On the client XP station open cmd prompt and run cliconfg - make sure the
enabled protocols match thos listed (or set) on the server using the process
above -

here http://www.tomshardware.co.uk/forum/167 ... nnect-1433

Now what is strange is that my laptop will load the svrnetcn as I run SQL Server Management Studio on it but the actual server says that it is an unknown command. It has both SSMS and SQL R2 on it. It will run the client config though. Do I really need to reinstall?

Tribble wrote:How do they open the port?

Not a DBA question.

One thing I have to ask, is this a named instance of SQL or did you use the default instance when installing

Named instances will not automatically use port 1433 http://msdn.microsoft.com/en-us/library/ms177440.aspx

It was default, now it is named

What's the latest on this

I now get a connection on 1433 but it drops or won't let it through. Perhaps there is something wrong with my setup. So I will be going through the config and seeing if there is anything I missed.

I reinstalled the "engine" and made it a named instance instead of the default. Will see how far the network guys get today. They cannot telnet the port but can see it.

Tribble wrote:They cannot telnet the port but can see it.

Wat.
Get them to do a netstat -an on the server and see if it is listening on port 1433, or whatever port SQL is set to use.
You should have left the default instance. I did not mean for you to install a named instance - that is not going to help at all...

Tribble wrote:I now get a connection on 1433 but it drops or won't let it through.

Do you see anything in the SQL error log on the server about refused connections?
Have you created a SQL user account that all of the ODBC connections will use to authenticate?

Named instances will only compound the issue as each named instance gets a different port to listen on. Better to have a default instance and make sure that the configuration is set to that it listens on the default port of 1433. Also the protocol used requires authentication to be performed before it will maintain the connection. That is why I said use SSMS (SQL Server Management Studio) to test with once you obtain marginal results with a telnet.

I left the network people there - will test this tomorrow.

For now I struggle with the code

Update:

I had an Sql guy come and check my server. Everything is correct. Data Pro now say we must open the port in Xp. Have no idea how to as anti-virus and firewalls have necessary exceptions. They cannot tell me how to either

what is this i dont even

The network guy is there and even he cannot access the ports

Tribsy, YGPM.

Going to look

Hi again

We are deploying on the laptops but we are experiencing issues.

On about 4 of the machines I set up a system dsn and it tests perfectly. But when we click ok - it disappears. Why would that happen?

One machine will open the access file but the rest say that it is an unrecognised format. I am running it on 3 PCs and one laptop without issues. All Access 2007. Could it be an incompatible element in the file?

Access 2007.. which service pack are they all on the same service pack was the file saved with Access 2007 and using the same service pack