| Author |
Topic |
|
kpsreenath
Yak Posting Veteran
52 Posts |
 Posted - 2006-04-11 : 11:51:19
|
| Informatica jobs were erroring out with a sql server driver errorwhile connecting to a named instance in SQL Server.The issue was because in sql server named instance, the port was allocated dynamically and Informatica 7.1.4 (provides 4.21 version of Datadirect odbc drivers for SQL server) which dont have support for named instances with dynamic port allocation. This feature is only present from version 5 of the Datadirect odbc drivers for SQL serverTo fix this issue, I need to allocate the port for the named instance instead of getting it dynamically.My questions1) Whats the impact of doing a port allocation for the named instance on the existing environment.2) Is there any issues which we can foresee in the applications3) Anything which i need to be aware ofThanksSree |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2006-04-11 : 11:56:11
|
| Assign a non default port. Normally default prots are targets for hackers!------------------------I think, therefore I am - Rene Descartes |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-04-11 : 12:21:54
|
| create an alias using the client network utility (type cliconfg from the command prompt).there are KB articles on how to do this also. Search microsoft.com for details.-ec |
|
|
|
kpsreenath
Yak Posting Veteran
52 Posts |
Posted - 2006-04-11 : 12:28:21
|
| When I am assigning a non default port, is there any changes to be done on the client applications end? To rephrase, Will the application still be able to listen on the static port for the named instance without any changes on the application end, as the SQL Server client library queries the server computer on UDP port 1434 to collect the information about the destination instance of SQL ServerInformation from the microsoft websiteDynamic port allocationOnly named instances of SQL Server can use the dynamic port allocation process. In the dynamic port allocation process, when you start the instance of SQL Server for the first time, the port is set to zero (0). Therefore, SQL Server requests a free port number from the operating system. As soon as a port number is allocated to SQL Server, SQL Server starts listening on the allocated port.The allocated port number is written to the Windows registry. Every time that you start that named instance of SQL Server, it uses that allocated port number. However, if another program that is already running on the computer is using that allocated port number when you start SQL Server, SQL Server chooses another port.When an instance of SQL Server uses dynamic port allocation, the connection string that is built at the SQL Server client does not specify the destination TCP/IP port unless the user or the programmer explicitly specifies the port. Therefore, the SQL Server client library queries the server computer on UDP port 1434 to collect the information about the destination instance of SQL Server. When SQL Server returns the information, the SQL Server client library sends the data to the appropriate instance of SQL Server.If UDP port 1434 is disabled, the SQL Server client cannot dynamically determine the port of the named instance of SQL Server. Therefore, the SQL Server client may not be able to connect to the named instance of SQL Server. In this situation, the SQL Server client must specify the dynamically allocated port where the named instance of SQL Server 2000 is listening.ThanksSree |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-04-11 : 12:43:52
|
| the solution is to create an alias on the client using the client configuration tool. This will solve your problem.-ec |
|
|
|
kpsreenath
Yak Posting Veteran
52 Posts |
Posted - 2006-04-11 : 13:30:42
|
| thanks ec...I will surely test this out..ThanksSree |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-04-11 : 21:04:08
|
| no it won't...it will still try to use any port available and you'll get varied results, atleast in our caseso i fixed the port numbers using the server network utility and the services must run on localsystemaccount, still searching if this locasystemaccount can be resolved ( it is a concern for us )hope MS will be able to improve on this in sql2k5for clients, if they are already set to using the port you're going to define, no need for changes on their aside otherwise you should inform them especially for those connection strings not using dsnHTH--------------------keeping it simple... |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-04-12 : 00:37:59
|
quote:
Originally posted by jen
no it won't...it will still try to use any port available and you'll get varied results, atleast in our caseso i fixed the port numbers using the server network utility and the services must run on localsystemaccount, still searching if this locasystemaccount can be resolved ( it is a concern for us )hope MS will be able to improve on this in sql2k5for clients, if they are already set to using the port you're going to define, no need for changes on their aside otherwise you should inform them especially for those connection strings not using dsnHTH--------------------keeping it simple...
Jen, I don't know what you are talking about. The dynamic port is determined only once (typically) for a SQL Server named instance. If there is a port conflict later on it is possible that SQL will start listening on a new port. But it never jumps around. We use tons of named instances on our boxes and I have to deal with this exact issue all the time.The dynamic port determination is something that occurs at the client, and this negotiation can be circumvented by using an alias and unchecking the "dynamically determine port" checkbox and filling in the port that the instance is using. There are a few reasons for software having a problem with named instances, they are (in my experience):1. MDAC release is too old to support named instance. anything older than 2.6 (as I recall) will have a problem. this means most copies of win2k.2. software cannot handle the '\' for the database name. This usually happens with older software.3. UDP port 1434 is blocked between the client and the database, which causes the dynamic port determination to fail.issue 1 is easily fixed. Issue 2 is usually fixed by upgradig the software. Issue 3 is the hardest to fix, because this is still considered a security hole for SQL Server (remember SQL Slammer?).-ec |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-04-12 : 07:55:22
|
so does that mean you don't restart your servers let's say for scheduled maintenance and then pray that sql will bind the previous port?i believe the poster would like to use static port to ensure connectivity all the time  , consistency is another way to put it,also effective when defining firewall rulesin some environments, ports are explicitly defined in the firewall so if it's dynamic how do you control which port will it use?--------------------keeping it simple... |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-04-12 : 12:15:11
|
quote:
Originally posted by jen
so does that mean you don't restart your servers let's say for scheduled maintenance and then pray that sql will bind the previous port?i believe the poster would like to use static port to ensure connectivity all the time , consistency is another way to put it,also effective when defining firewall rulesin some environments, ports are explicitly defined in the firewall so if it's dynamic how do you control which port will it use?--------------------keeping it simple...
named instances will use the same port all the time. They will not switch ports after a restart. Usually, the only time it chooses a random port is the first time you start it up. I say usually because SQL Server will switch to a new random port if there is a port conflict when it starts up. This is extremely rare, especially if you don't install other services on your SQL boxes and have an idea of what ports your SQL instances are using.-ec |
|
|
|
|
SQL Server named instances