Specify SQL Server Port

  • Thread starter Thread starter Mark Aslan Kuschel
  • Start date Start date
M

Mark Aslan Kuschel

Hello,

due to security reasons I changed the port of my SQL Server and activated
Windows Firewall (it runs w2k3).
For administering the database I'd like to use Microsoft Access 2003, but it
does no longer find the SQL Server instance.

In a SQL Connection String I can write: datasource=servername,port
But Access does not seem to understand that, also the regular
servername:port is not understood by Access.

Is there a way to specify the port Access connects to?

Thanks for any answer :-)

Regards,
Mark Aslan Kuschel

Irgendware Software Solutions GbR
http://www.irgendware.com
 
Hi Mark,

Thanks for using MSDN Newsgroup!

From your descriptions, I understood that you would like to connect SQL
Server by means of Access, but you are trapped in its data source
definition. Have I understood you? If there is anything I misunderstood,
please feel free to let me know:)

First of all, you are using ADO in Access, aren't you? Based on my
knowledge, you normally write the connection codes like this
(6500 is the custom port in this example.)

Provider=SQLOLEDB; Data Source=Server, 6500; Initial Catalog=pubs; User
ID=sa;
Password=;

Have you notice the blank between data and source? Could you try using the
connection string again to see whether it works fine?

Secondly, I think the following document will be helpful, which illustrated
how to use ADO under a firewall

HOWTO: Use ADO to Connect to a SQL Server That Is Behind a Firewall (269882)
http://support.microsoft.com/?id=269882

Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
 
Hello Mingqing,

Thanks for your answer.
Yes, I am trapped in that data source definition.

I have no problems to specify a connection string for my ADO.Net driven
programs, it works fine. But accessing the database directly in Microsoft
Access does not work like I try it.
In Access I tried to change the connection parameters in the dialog
File->Connection...
(I made a screenshot that is avaiable at
http://www.mark-kuschel.de/bilder/AccessSQLServer.JPG)
Since there is no way in Access to enter that parameters via a connection
string e.g. like in Visual Studio.Net (if it is, please let me know how :-))
I use that dialog and I am stuck in finding out a way to enter the port
number there.

Thanks again,
Mark Aslan Kuschel

Irgendware Software Solutions GbR
http://www.irgendware.com
 
Hi Mark,

Thanks for your prompt updates!

From your screenshot, I found you are using DataAdapter in VS.NET
connecting SQL Server. Have I understood you?

Although I am not an expert on VS.NET, I would like to give a gerneral idea
about connecting SQL Server with another port. I will show you the way
opening port 1869 and then using DataAdapter connecting it :)
1. Start -> Microsoft SQL Server -> Server Network Utility (Notice, not
Client Network Utility)
2. In the box of "Enabed protocols:", select TCP/IP and then click
properties
3. Change default port to 1869
4. stop SQL Server service and then start it again

These steps above will successfully open port 1869 and let's confirm it:
1. Start -> Run, typing "cmd"
2. In the DOS Prompt, run the command "osql -S yourServerName,1869 -U user
-P password"
Try to see whether you could see "1>" or error message, what's the error
message?
If you could see "1>" that means your 1869 port has open correctly.

At last, Open VS.NET DataAdapter, typing the codes like what in your
screenshot "yourServerName,1869"

All above runs well on my machine and I could connect my port 1869 with
that kind of configuration. If you still could not make it, would you
please so kind as to show me some log files in SQL Server? Could you the
string like these in SQL Log file "SQL Server listening on [IP Address]:
1869"?

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
 
Hi Mark,

I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!


Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
 
Hi Mark,

Based on my testing, the codes like

Provider=SQLOLEDB; Data Source=Server, 6500; Initial Catalog=pubs; User
ID=sa; Password=;

could run well on my machine. So would you please follow my suggestions in
the previous post to configure port and then try it in Access again?


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
 
Hi Mark,

Based on my testing, the codes like

Provider=SQLOLEDB; Data Source=Server, 6500; Initial Catalog=pubs; User
ID=sa; Password=;

could run well on my machine. So would you please follow my suggestions in
the previous post to configure port and then try it in Access again?


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
 
Hello Mingqing,

last days I had no time to look up this newsgroup, thanks for your answers.
My screenshot is not taken from VS.Net, it is taken from Microsoft Access
2003, that's why I'm posting to this newsgroup.
But the dialog in VS.Net looks nearly similar, I did not have in mind :-)

Now I installed Access 2003 also on the server, and there it is working.
But from other computers I still cannot connect.
Loggin in with oSQL works fine, I tried it local on that server and also
from my server at home over internet. The port is open...

But trying to connect over Visual Studio or Access still throws the
following error:
SQL State: '08001'
SQL-Server Error: 17
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.

This is very confusing...

The Log of my SQL Server is at the end of this post, it looks pretty good, I
cannot see any problem there.
I also activated logging of Windows Firewall, but it did not log any entries
from my IP.

Thanks,
Mark

2004-06-14 11:54:21.58 server Microsoft SQL Server 2000 - 8.00.760
(Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )

2004-06-14 11:54:21.58 server Copyright (C) 1988-2002 Microsoft
Corporation.
2004-06-14 11:54:21.58 server Alle Rechte vorbehalten.
2004-06-14 11:54:21.58 server Serverprozess-ID ist 8896.
2004-06-14 11:54:21.58 server Protokolliert SQL Server-Meldungen in Datei
'C:\Programme\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2004-06-14 11:54:21.62 server SQL Server startet mit Prioritätsklasse
'normal' (1 CPU vorgefunden).
2004-06-14 11:54:21.65 server SQL Server wurde für die Verarbeitung im
thread-Modus konfiguriert.
2004-06-14 11:54:21.65 server Die dynamic-Sperrenreservierung wird
verwendet. [2500] Sperrenblöcke, [5000] Sperrenbesitzerblöcke.
2004-06-14 11:54:21.65 server Initialisiert Distributed Transaction
Coordinator.
2004-06-14 11:54:22.79 spid3 Startet Datenbank 'master'.
2004-06-14 11:54:23.26 server Verwendet 'SSNETLIB.DLL', Version
'8.0.766'.
2004-06-14 11:54:23.37 server SQL Server überwacht xx.xx.xx.xx: 1869. (my
public IP adress replaced by xx.xx.xx.xx)
2004-06-14 11:54:23.37 server SQL Server überwacht 127.0.0.1: 1869.
2004-06-14 11:54:23.37 spid5 Startet Datenbank 'model'.
2004-06-14 11:54:23.41 spid3 Servername ist 'IR-DATENBANK'.
2004-06-14 11:54:23.41 spid8 Startet Datenbank 'msdb'.
2004-06-14 11:54:23.41 spid9 Startet Datenbank 'pubs'.
2004-06-14 11:54:23.41 spid10 Startet Datenbank 'DotTextDB'.
2004-06-14 11:54:23.41 spid11 Startet Datenbank 'Irgendware'.
2004-06-14 11:54:23.46 server SQL Server überwacht TCP, Named Pipes.
2004-06-14 11:54:23.46 server SQL Server ist bereit für
Clientverbindungen
2004-06-14 11:54:24.71 spid5 Löscht tempdb-Datenbank.
2004-06-14 11:54:24.73 spid9 Startet Datenbank 'Merle'.
2004-06-14 11:54:25.12 spid8 Startet Datenbank 'Infrared'.
2004-06-14 11:54:26.80 spid9 Startet Datenbank 'PC'.
2004-06-14 11:54:28.12 spid8 Startet Datenbank 'Oeg2004'.
2004-06-14 11:54:28.46 spid9 Startet Datenbank 'SUS_Logs'.
2004-06-14 11:54:28.69 spid10 Startet Datenbank 'Irgendware-Test'.
2004-06-14 11:54:29.62 spid5 Startet Datenbank 'tempdb'.
2004-06-14 11:54:31.63 spid3 Wiederherstellung abgeschlossen.
2004-06-14 11:54:31.63 spid3 SQL global counter collection task is
created.
 
Hi Mark,

Thanks for your prompt updates!

From your descriptions, I understood that you still could not connect to
your server with another machine and it reports the error 17. Have I
understood you? If there is anything I misunderstood, please feel free to
let me know:)

Based on my scope, would you please do the following steps to
troubleshooting this issue? (All the steps are to be taken in the client)
1. What's your osql command? could you still logged in with your command
like this

osql -S <server ip>,1869> -U username -P password

2. Would you please try to connect server with the following command (still
in a Dos prompt)

telnet <server ip> 1869
(NOTICE, there is a blank between <server ip> and 1869)

What's the result? Will you get the error message "Could not open
connection to host, on port 1869"? If you get this one, it means your
server is not configurated correctly. The following documents may be helpful

INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/?id=287932

HOWTO: Use ADO to Connect to a SQL Server That Is Behind a Firewall
http://support.microsoft.com/?id=269882

3. Could you get the right result from command (Dos-prompt)?
ping <server ip>


Hope it helps and if you have any questions or concerns, don't hesitate to
let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support
 
Hi Mingquing,

You have me understood correctly.

My oSQL Command is:
oSQL -S <server ip>, 1869 -U username
Then I'm asked for a password

Your command does not work, I think there is a typo, the > after the port
should not be there
But:
osql -S <server ip>,1869 -U username -P password
works perfectly.

Connecting with Telnet on Port 1869 works also, I get a blank cmd window
then.
But when I enter 8 keys I get disconnected.

Pinging also works fine; within subnet:
Antwort von <server ip>: Bytes=32 Zeit<1ms TTL=128

Thanks :-)

Regards,
Mark
 
Back
Top