Troubled Tribble needs SQL/VBA help

Get help on databases - MySQL, Oracle, Access, etc.
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Troubled Tribble needs SQL/VBA help

Post by Tribble »

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........
Last edited by jee on 30 May 2011, 12:42, edited 3 times in total.
Reason: Don't like the Dumb
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Linking to SQL 2008 R2

Post by RuadRauFlessa »

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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Linking to SQL 2008 R2

Post by hamin_aus »

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.
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

I know. Working on that now thanks. Will let you know how it goes.
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

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.
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

Ha ha the telnet thing didn't work. I have asked them to make it accessible. Thanks I could hug you
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

How do they open the port?
Image
GDI_Lord
Forum Administrator
Posts: 2663
Joined: 05 Sep 2005, 02:00

Re: Linking to SQL 2008 R2

Post by GDI_Lord »

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.
Important Thread: Yours in Christ's service,
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

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
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

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.
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

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?
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Linking to SQL 2008 R2

Post by hamin_aus »

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 :arrow: http://msdn.microsoft.com/en-us/library/ms177440.aspx
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

It was default, now it is named
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Linking to SQL 2008 R2

Post by hamin_aus »

What's the latest on this :?:
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

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.
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Linking to SQL 2008 R2

Post by hamin_aus »

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?
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Linking to SQL 2008 R2

Post by RuadRauFlessa »

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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

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

For now I struggle with the code
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

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
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Linking to SQL 2008 R2

Post by hamin_aus »

what is this i dont even
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

The network guy is there and even he cannot access the ports
Image
GDI_Lord
Forum Administrator
Posts: 2663
Joined: 05 Sep 2005, 02:00

Re: Linking to SQL 2008 R2

Post by GDI_Lord »

Tribsy, YGPM.
Important Thread: Yours in Christ's service,
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking to SQL 2008 R2

Post by Tribble »

Going to look
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Linking SQL - New issue

Post by Tribble »

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?
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Linking to SQL 2008 R2

Post by RuadRauFlessa »

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 :?:
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Post Reply