Bug in SQLDMO SQLServer.Connect if server is behind a firewall

Apparently the Connect method (as well as the PingSQLVersion method) of the
SQLServer object in the SQLDMO library does not honor any of the timeout
property values (BlockingTimeout, LoginTimeout, or QueryTimeout) if a remote
server is firewalled.
Apparently if the remote server is firewalled, the connection attempt will
always timeout after about 45 seconds.
What is interesting is the server responds and is added to the NameList
object when an Application.ListAvailableSQLServers method call is made. I
believe this works by sending a broadcast on UDP port 1434, correct? So why
would this respond even though the port is firewalled -- that's what I find
a
bit odd, or at the very least inconsistent.
Granted, if the server is local and it's firewalled, it will connect. I'm
guessing SQLServer.Connect has a few methods in which it tries to make the
connection.
I've been able to reproduce this in a consistent manner dozens of times. I
guess my question is whether or not there's a workaround or something else I
can do to prevent my application from waiting until that timeout limit is
reached.Hi
Are you sure the firewall does not have specific rules that allows you to
connect on the port rather than a blanket block?
John
"Rob Gomes" wrote:

> Apparently the Connect method (as well as the PingSQLVersion method) of th
e
> SQLServer object in the SQLDMO library does not honor any of the timeout
> property values (BlockingTimeout, LoginTimeout, or QueryTimeout) if a remo
te
> server is firewalled.
> Apparently if the remote server is firewalled, the connection attempt will
> always timeout after about 45 seconds.
> What is interesting is the server responds and is added to the NameList
> object when an Application.ListAvailableSQLServers method call is made. I
> believe this works by sending a broadcast on UDP port 1434, correct? So w
hy
> would this respond even though the port is firewalled -- that's what I fin
d a
> bit odd, or at the very least inconsistent.
> Granted, if the server is local and it's firewalled, it will connect. I'm
> guessing SQLServer.Connect has a few methods in which it tries to make the
> connection.
> I've been able to reproduce this in a consistent manner dozens of times.
I
> guess my question is whether or not there's a workaround or something else
I
> can do to prevent my application from waiting until that timeout limit is
> reached.|||Not sure. This is the firewall provided in Windows XP.
Moreover, what I'm more concerned with is the fact that when calling the
Connect() method, instead of failing immediately (or after the LoginTimeout
expires) it times out after 45 seconds -- despite what I define for the
timeout properties for the SQLServer or SQLServer2 object.
"John Bell" wrote:
> Hi
> Are you sure the firewall does not have specific rules that allows you to
> connect on the port rather than a blanket block?
> John
> "Rob Gomes" wrote:
>|||Only LoginTimeout has any effect on this behavior, BlockingTimeout and
QueryTimeout have no affect.
Which client protocols are enabled and what is the default protocol?
If I am forcing TCP it works on my machine as expected, when the firewall is
closed it returns after 15 seconds, using the following code sample
using System;
using System.Runtime.InteropServices;
using SQLDMO;
namespace SDN
{
/// <summary>
///
/// </summary>
class App
{
[MTAThread]
static void Main(string[] args)
{
try
{
SQLServer2Class s = new SQLServer2Class();
s.LoginSecure = true;
s.LoginTimeout = 15;
s.Connect("tcp:gertd00,1433", null, null);
s.DisConnect();
}
catch(System.Runtime.InteropServices.COMException ex)
{
Console.WriteLine(ex);
}
catch(System.Exception ex)
{
Console.WriteLine(ex);
}
}
}
}
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Rob Gomes" <RobGomes@.discussions.microsoft.com> wrote in message
news:DD239C68-DC06-42B0-96B8-67DDEBB7EE90@.microsoft.com...
> Not sure. This is the firewall provided in Windows XP.
> Moreover, what I'm more concerned with is the fact that when calling the
> Connect() method, instead of failing immediately (or after the
> LoginTimeout
> expires) it times out after 45 seconds -- despite what I define for the
> timeout properties for the SQLServer or SQLServer2 object.
> "John Bell" wrote:
>

Bug in Microsoft SQL Server 2005 JDBC Driver

I am using the new Microsoft SQL Server 2005 JDBC Driver to connect to ms sql 2000. I am testing GMT date conversions in my application and found a bug in the way the driver converts DateTime columns in the ResultSet.getTimeStamp(int i, Calendar cal) method.

The driver is not converting GMT dates to client time correctly in the time range og April 1, 9:00 pm to 10:00 pm EST through the rs.getTimeStamp(int, Calendar) method. The date is coming back an hour off. In UTC time, this time is April 2, 2:00 AM to April 2, 3:00 AM. I guess daylight savings is confusing it. Can someone get back to me with a work around or verification of this bug. Thanks.

Sequence:

1. Set client and server to April 1, 9:05 PM EST. (This is the day before Daylight savings occurs).

2. Run an query on the client from java using the driver.

Calendar cal = Calendar.getInstance();
cal.setTimeZone(TimeZone..getTimeZone("UTC");
Date d;
ResultSet rs = stmt.executeQuery("SELECT getutcdate()");
while(rs.next())
{
d = rs.getTimeStamp(0, cal);
}

System.err.println(d);

Hi Harry,

I think I already replied to you in private email, but I wanted to make sure others scouring the forum are aware as well. This is a bug in the v1.0 SQL Server 2005 JDBC driver. Microsoft will be providing a QFE with a fix. I'll follow up with another post as soon as it becomes available. We are taking some extra time with the fix to make sure it is correct with respect to the subtle nuances of Java's Calendar object, like Daylight Savings Time start and end dates, and historical time zone offset changes.

Thank you for your patience.

--David Olix

JDBC Development

|||

This bug has been now been fixed as a QFE. Please notify CSS that you would like to get the hotfix for KB article 917055.

Again, thank you for your patience,

--David Olix

JDBC Development

Bug in Microsoft SQL Server 2005 JDBC Driver

I am using the new Microsoft SQL Server 2005 JDBC Driver to connect to ms sql 2000. I am testing GMT date conversions in my application and found a bug in the way the driver converts DateTime columns in the ResultSet.getTimeStamp(int i, Calendar cal) method.

The driver is not converting GMT dates to client time correctly in the time range og April 1, 9:00 pm to 10:00 pm EST through the rs.getTimeStamp(int, Calendar) method. The date is coming back an hour off. In UTC time, this time is April 2, 2:00 AM to April 2, 3:00 AM. I guess daylight savings is confusing it. Can someone get back to me with a work around or verification of this bug. Thanks.

Sequence:

1. Set client and server to April 1, 9:05 PM EST. (This is the day before Daylight savings occurs).

2. Run an query on the client from java using the driver.

Calendar cal = Calendar.getInstance();
cal.setTimeZone(TimeZone..getTimeZone("UTC");
Date d;
ResultSet rs = stmt.executeQuery("SELECT getutcdate()");
while(rs.next())
{
d = rs.getTimeStamp(0, cal);
}

System.err.println(d);

Hi Harry,

I think I already replied to you in private email, but I wanted to make sure others scouring the forum are aware as well. This is a bug in the v1.0 SQL Server 2005 JDBC driver. Microsoft will be providing a QFE with a fix. I'll follow up with another post as soon as it becomes available. We are taking some extra time with the fix to make sure it is correct with respect to the subtle nuances of Java's Calendar object, like Daylight Savings Time start and end dates, and historical time zone offset changes.

Thank you for your patience.

--David Olix

JDBC Development

|||

This bug has been now been fixed as a QFE. Please notify CSS that you would like to get the hotfix for KB article 917055.

Again, thank you for your patience,

--David Olix

JDBC Development

browsin from excel

i can connect olap cubes from excel on any client with blank username and password, although i defined roles and users, a user who skips username and password fields can see all cubes wity any browsing tool ( Proclarity, excel etc )

thx in advance

The username and password field in Excel is only used if you have setup HTTP authentication. Otherwise windows integrated security is used. This means that you must have these users or a group that they are members of in a server or database role that has permission to browse the cubes.

Brought down passive cluster - now getting "SQL Server does not ex

I turned off the passive part of the cluster. When I try to connect via DSN,
to an instance of SQL ServerI receive the error "SQL Server does not exist".
Is this due to the fact that the passive cluster is shutdown?
What are the properties of your DSN...maybe you have it set to the the Node,
not the Virtual SQL Server name?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:8E20474F-1F64-477D-A2DC-44A4DD442CE5@.microsoft.com...
>I turned off the passive part of the cluster. When I try to connect via
>DSN,
> to an instance of SQL ServerI receive the error "SQL Server does not
> exist".
> Is this due to the fact that the passive cluster is shutdown?

broke sql server...please help

so i installed sql server and i was using it within enterprise manager. Then it broke when i started trying to make my web application connect to my database. So my connection string was:

server=(local);database=capacity;User ID=dbo;trusted_connection=False

i got this error: Login failed for user 'dbo'. Reason: Not associated with a trusted SQL Server connection.

so i tried messing around with the string and found something online that said i should right click the server (local) and go to the security settings and change it to mixed mode, sql server authentication AND windows authentication. Before it was just windows authentication. So i did that...and now i get this error when i try to open up my server in enterprise manager:

A connection could not be established to (local)
Reason: Login failed for user 'samr'. Reason Not associated with a trusted SQL Server Connection..

Please verify SQL Server is running (which it is) and check your SQL server registration properties by right clicking on the local node and try again.

i can right click on it, but whenever i do anything (go to properties, click the '+' icon) i get this message:

Connection failed. Check sql server registration properties.

please help...

should i delete the registration and just start over...?

why couldnt i connect into sql server...?Take a look @.INF: Windows Authentication is the Default Security Mode After a SQL Server 2000 Typical Installation

Bringing Standby server online when in "no recovery mode"

Hi,

I am trying to set up Log Shipping. Though I have the following problem...
I want to set up LS without any user being able to connect. As I understand, this means I have to select the "No recovery mode" in the "Add Destination Database" window when creating the Log Shipping Maintenance Plan.
This all works fine. I set this up, no user can connect to the database (it shows status: (Loading) ), perfect.

However, the problem I encounter is when I want to bring the Standby server online.
I run the 'sp_change_primary_role' SP on the primary server... works fine.
I run the 'sp_change_secondary_role' on the standby server... errors:

Server: Msg 927, Level 14, State 2, Line 1
Database 'Northwind' cannot be opened. It is in the middle of a restore.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 14440, Level 16, State 1, Procedure sp_change_secondary_role, Line 49
Could not set single user mode.

So what am I doing wrong? Am I forgetting something? I think i must do. The only way to get the secondary (standby) online is to do a "detach - attach", as far as I know.
I can not check its Properties or do a restore or backup of this database either.

Any help would be very much appreciated.

Thanks in advance and kind regards,

Jan V.Run the following command, to open the database:

restore database (name of database) with recovery

This will trigger a recovery of the database, and reset the database to "open" from "loading"