|
Home > Archive > SQL Server JDBC > January 2006 > Bug with instanceName property?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Bug with instanceName property?
|
|
| Wes Clark 2006-01-12, 8:23 pm |
| I can only get a connection to server with a non-default instance name using
the
jdbc:sqlserver:// ITH\SQLSERVER2005:14
33;forwardReadOnlyMe
thod=direct;database
Name=cc;user=sa;pass
word=123
syntax. This connection succeeds.
If I try to use
jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005/ user=sa;forwardReadO
nlyMethod=direct;dat
abaseName=cc;passwor
d=123
following the example in the docs delivered with the Beta 2, I get
com.microsoft.sqlserver.jdbc.SQLServerException: Failed
Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection.
If I use the syntax
jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;user
=sa;forwardReadOnlyM
ethod=direct;databas
eName=cc;password=12
3
I get
com.microsoft.sqlserver.jdbc.SQLServerException: Failed
Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database
requested in login 'cc'. Login fails.
Any suggestions?
| |
| Angel Saenz-Badillos[MS] 2006-01-12, 8:23 pm |
| The doc example is unfortunatelly wrong, it should use ";" instead of "/" to
separate instanceName and user. As far as the last connection string, the
first thing that stands out is that you are specifying a port number (1433).
When you specify a port it will take precedence over the instance name.
(Note, I am actually not sure of the behavior of the beta 2 driver here,
this will be true for the final version shipping this month).
Looking at the connection string another thing stands out. Due to customer
feedback we have decided to rename forwardReadOnlyMetho
d back to its
previous name, selectMethod. Apparently nobody has any idea of what the new
name meant. You will need to make sure to update this when moving your code
to the final driver. This is important because you _will not_ get an
exception for connection key value pairs that are not understood by the
driver. I am not a big fan of silently ignoring key value pairs but this
change was necesary for certification purposes.
--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
"Wes Clark" < WesClark@discussions
.microsoft.com> wrote in message
news:5C247C15-D945-4D81-A646- B41F20C11F50@microso
ft.com...
>I can only get a connection to server with a non-default instance name
>using
> the
> jdbc:sqlserver:// ITH\SQLSERVER2005:14
33;forwardReadOnlyMe
thod=direct;database
Name=cc;user=sa;pass
word=123
> syntax. This connection succeeds.
> If I try to use
> jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005/ user=sa;forwardReadO
nlyMethod=direct;dat
abaseName=cc;passwor
d=123
> following the example in the docs delivered with the Beta 2, I get
> com.microsoft.sqlserver.jdbc.SQLServerException: Failed
> Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for
> user
> '(null)'. Reason: Not associated with a trusted SQL Server connection.
>
> If I use the syntax
> jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;user
=sa;forwardReadOnlyM
ethod=direct;databas
eName=cc;password=12
3
> I get
> com.microsoft.sqlserver.jdbc.SQLServerException: Failed
> Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open
> database
> requested in login 'cc'. Login fails.
>
> Any suggestions?
| |
| Wes Clark 2006-01-12, 8:23 pm |
| I appreciate your speedy reply. What is the relationship between the
instanceName and the port number? I installed SQL Server 2005 on a machine
that already had SQL Server 2000 on it, so that's why I gave it an instance
name different from the default. I've also read that by specifying the port,
you get a more efficient connection since it doesn't need to connect to the
SQL Browser service first.
I see now from the help file on the Network Configuration help file that
named instances always use dynamic ports, so when I specify the port 1433, I
am specifically requested the default instance on that box. Perhaps the
documentation for port number and instanceName could mention that non-default
instance names always use dynamic ports, so that specifying a port doesn't
make sense.
What is interesting is the connection will succeed unless I specify a
database name. To wit,
jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;user
=sa;password=123;
Connected!
Kind of strange. I suggest the
jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;forw
ardReadOnlyMethod=di
rect;user=sa;passwor
d=123;
Connected!
jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;forw
ardReadOnlyMethod=di
rect;databaseName=cc
;user=sa;password=12
3;
com.microsoft.sqlserver.jdbc.SQLServerException: Failed
Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database
requested in login 'cc'. Login fails.
I can confirm what you are saying, however, by removing the port
specification when I use the instanceName parameter syntax:
jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;user=sa;f
orwardReadOnlyMethod
=direct;databaseName
=cc;password=123
Connected!
| |
| Angel Saenz-Badillos[MS] 2006-01-13, 8:23 pm |
| This is actually easier to explain in pseudo code. It works something like:
If you specify a portnumber attempt to connect using port number.
//attempt to connect 1 round trip.
else if you specify instance name
Query SqlBrowser for port number of instance name specified //1
roundtrip
//attempt to connect. 2nd round trip
It is much cheaper for us to connect to a server when you specify the port
number (1 round trip versus 2), so every time you specify a port number we
will avoid the roundtrip to query the sqlbrowser (ignore the instance name
you specify) and attempt to connect to the server on the port you specify.
Does this make sense?
--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
"Wes Clark" < WesClark@discussions
.microsoft.com> wrote in message
news:DF398F67-AE3B-4412-9CED- 72A41EAB35D8@microso
ft.com...
>I appreciate your speedy reply. What is the relationship between the
> instanceName and the port number? I installed SQL Server 2005 on a
> machine
> that already had SQL Server 2000 on it, so that's why I gave it an
> instance
> name different from the default. I've also read that by specifying the
> port,
> you get a more efficient connection since it doesn't need to connect to
> the
> SQL Browser service first.
>
> I see now from the help file on the Network Configuration help file that
> named instances always use dynamic ports, so when I specify the port 1433,
> I
> am specifically requested the default instance on that box. Perhaps the
> documentation for port number and instanceName could mention that
> non-default
> instance names always use dynamic ports, so that specifying a port doesn't
> make sense.
>
> What is interesting is the connection will succeed unless I specify a
> database name. To wit,
> jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;user
=sa;password=123;
> Connected!
>
> Kind of strange. I suggest the
> jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;forw
ardReadOnlyMethod=di
rect;user=sa;passwor
d=123;
> Connected!
> jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;forw
ardReadOnlyMethod=di
rect;databaseName=cc
;user=sa;password=12
3;
> com.microsoft.sqlserver.jdbc.SQLServerException: Failed
> Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open
> database
> requested in login 'cc'. Login fails.
>
> I can confirm what you are saying, however, by removing the port
> specification when I use the instanceName parameter syntax:
> jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;user=sa;f
orwardReadOnlyMethod
=direct;databaseName
=cc;password=123
> Connected!
>
| |
| Wes Clark 2006-01-13, 8:23 pm |
| I understand the aded efficiency of specifying a port number, and that only
default -named instances can have a fixed port.
What I find interesting is the connection will succeed unless I specify a
database name. To wit,
jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;user
=sa;password=123;
Connected!
jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;forw
ardReadOnlyMethod=di
rect;user=sa;passwor
d=123;
Connected!
jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;forw
ardReadOnlyMethod=di
rect;databaseName=cc
;user=sa;password=12
3;
com.microsoft.sqlserver.jdbc.SQLServerException: Failed
Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database
requested in login 'cc'. Login fails.
| |
| Angel Saenz-Badillos[MS] 2006-01-14, 3:23 am |
| The only thing I can think of is that the Sql server 2000 you have installed
on that machine at port 1433 has a user=sa, password=123 and it does not
have a database called cc.
What do you get when you try the following:
jdbc:sqlserver:// ITH:1433;user=sa;pas
sword=123;
jdbc:sqlserver:// ITH:1433;forwardRead
OnlyMethod=direct;us
er=sa;password=123; jdbc:sqlserver:// ITH:1433;forwardRead
OnlyMethod=direct;da
tabaseName=cc;user=s
a;password=123;what about this: jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;user=sa;p
assword=123
; jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;forwardRe
adOnlyMethod=direct;
user=sa;password=123
;jdbc:sqlserver:// ITH:;instanceName=SQ
LSERVER2005;forwardR
eadOnlyMethod=direct
;databaseName=cc;use
r=sa;password=123;--Angel Saenz-Badillos [MS] DataWorksThi
s posting is provided "AS IS", with no warranties, and confers norights.Please do not send email directly to this alias.This alias is for newsgroup purposes only.I am now blogging: http://weblogs.asp.net/angelsb/"Wes Clark" < WesClark@discussions
.microsoft
.com> wrote in messagenews:5974EAF9
-1FD7-495C-A919- FED1E718EB47@microso
ft.com...>I understand the aded efficiency of specifying a port number, and that only> default -named instances can have a fixed port.>> What I find interesting is the connection will
succeed unless I specify a> database name. To wit,>> jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;user
=sa;password=123;> Connected!>>jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;forw
ardReadOnlyMethod=di
rect;user=sa;passwor
d=123;> Connected
!>>jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;forw
ardReadOnlyMethod=di
rect;databaseName=cc
;user=sa;password=12
3;> com.microsoft.sqlserver.jdbc.SQLServerException: Failed> Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Cannot opendatabase
> requested in login 'cc'. Login fails.>
| |
| Angel Saenz-Badillos[MS] 2006-01-14, 3:23 am |
| I am sorry the previous message is not legible for some reason. trying
again:
The only thing I can think of is that the Sql server 2000 you have installed
on that machine at port 1433 has a user=sa, password=123 and it does not
have a database called cc.
What do you get when you try the following:
jdbc:sqlserver:// ITH:1433;user=sa;pas
sword=123;
jdbc:sqlserver:// ITH:1433;forwardRead
OnlyMethod=direct;us
er=sa;password=123;
jdbc:sqlserver:// ITH:1433;forwardRead
OnlyMethod=direct;da
tabaseName=cc;user=s
a;password=123;
what about this:
jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;user=sa;p
assword=123;
jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;forwardRe
adOnlyMethod=direct;
user=sa;password=123
;
jdbc:sqlserver:// ITH:;instanceName=SQ
LSERVER2005;forwardR
eadOnlyMethod=direct
;databaseName=cc;use
r=sa;password=123;
| |
| Wes Clark 2006-01-14, 3:23 am |
| Your theory is correct. I thought I had stopped the SQL Server 2000 service
when I was running my tests so I would not get a successful connection if it
was trying to connect to it. Here are the results
jdbc:sqlserver:// ITH:1433;user=sa;pas
sword=123;
Connected!
jdbc:sqlserver:// ITH:1433;forwardRead
OnlyMethod=direct;us
er=sa;password=123;
Connected!
jdbc:sqlserver:// ITH:1433;forwardRead
OnlyMethod=direct;da
tabaseName=cc;user=s
a;password=123;
com.microsoft.sqlserver.jdbc.SQLServerException: Failed
Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database
requested in login 'cc'. Login fails.
jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;user=sa;p
assword=123;
Connected!
jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;forwardRe
adOnlyMethod=direct;
user=sa;password=123
;
Connected!
jdbc:sqlserver:// ITH:;instanceName=SQ
LSERVER2005;forwardR
eadOnlyMethod=direct
;databaseName=cc;use
r=sa;password=123;
com.microsoft.sqlserver.jdbc.SQLServerException: Failed
Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Invalid port number:
You might consider clearing up this logical inconsistentcy. If I specify a
port with the syntax
jdbc:sqlserver:// ITH\SQLSERVER2005:14
33;forwardReadOnlyMe
thod=direct;database
Name=cc;user=sa;pass
word=123;
Connected!
it succeeds, but with the syntax
jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;forw
ardReadOnlyMethod=di
rect;databaseName=px
;user=sa;password=12
3;
com.microsoft.sqlserver.jdbc.SQLServerException: Failed Logon:
it fails. Maybe there is a good reason for it, but from my perspective, the
two are just different ways of specifying the same thing.
Again, thanks for you time.
"Angel Saenz-Badillos[MS]" wrote:
> I am sorry the previous message is not legible for some reason. trying
> again:
>
> The only thing I can think of is that the Sql server 2000 you have installed
> on that machine at port 1433 has a user=sa, password=123 and it does not
> have a database called cc.
>
> What do you get when you try the following:
> jdbc:sqlserver:// ITH:1433;user=sa;pas
sword=123;
> jdbc:sqlserver:// ITH:1433;forwardRead
OnlyMethod=direct;us
er=sa;password=123;
> jdbc:sqlserver:// ITH:1433;forwardRead
OnlyMethod=direct;da
tabaseName=cc;user=s
a;password=123;
>
> what about this:
> jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;user=sa;p
assword=123;
> jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;forwardRe
adOnlyMethod=direct;
user=sa;password=123
;
> jdbc:sqlserver:// ITH:;instanceName=SQ
LSERVER2005;forwardR
eadOnlyMethod=direct
;databaseName=cc;use
r=sa;password=123;
>
>
>
| |
| Angel Saenz-Badillos[MS] 2006-01-15, 3:23 am |
| The logical inconsistency that you mention is clearly a bug. I just tried
this on the latest internal builds and it will fail to connect in _both_
cases.( In both cases port number will take precedence.
--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
"Wes Clark" < WesClark@discussions
.microsoft.com> wrote in message
news:860ED5EE-1F40-46AE-948E- CA4BB44F911F@microso
ft.com...[color=darkred]
> Your theory is correct. I thought I had stopped the SQL Server 2000
> service
> when I was running my tests so I would not get a successful connection if
> it
> was trying to connect to it. Here are the results
>
> jdbc:sqlserver:// ITH:1433;user=sa;pas
sword=123;
> Connected!
>
> jdbc:sqlserver:// ITH:1433;forwardRead
OnlyMethod=direct;us
er=sa;password=123;
> Connected!
>
> jdbc:sqlserver:// ITH:1433;forwardRead
OnlyMethod=direct;da
tabaseName=cc;user=s
a;password=123;
> com.microsoft.sqlserver.jdbc.SQLServerException: Failed
> Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open
> database
> requested in login 'cc'. Login fails.
>
> jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;user=sa;p
assword=123;
> Connected!
>
> jdbc:sqlserver:// ITH;instanceName=SQL
SERVER2005;forwardRe
adOnlyMethod=direct;
user=sa;password=123
;
> Connected!
>
> jdbc:sqlserver:// ITH:;instanceName=SQ
LSERVER2005;forwardR
eadOnlyMethod=direct
;databaseName=cc;use
r=sa;password=123;
> com.microsoft.sqlserver.jdbc.SQLServerException: Failed
> Logon:com.microsoft.sqlserver.jdbc.SQLServerException: Invalid port
> number:
>
> You might consider clearing up this logical inconsistentcy. If I specify
> a
> port with the syntax
>
> jdbc:sqlserver:// ITH\SQLSERVER2005:14
33;forwardReadOnlyMe
thod=direct;database
Name=cc;user=sa;pass
word=123;
> Connected!
>
> it succeeds, but with the syntax
>
> jdbc:sqlserver:// ITH:1433;instanceNam
e=SQLSERVER2005;forw
ardReadOnlyMethod=di
rect;databaseName=px
;user=sa;password=12
3;
> com.microsoft.sqlserver.jdbc.SQLServerException: Failed Logon:
>
> it fails. Maybe there is a good reason for it, but from my perspective,
> the
> two are just different ways of specifying the same thing.
>
> Again, thanks for you time.
>
> "Angel Saenz-Badillos[MS]" wrote:
>
| |
| Wes Clark 2006-01-17, 3:23 am |
| Do I need to do anything else to report the bug, or do you have the ball now?
| |
| Angel Saenz-Badillos[MS] 2006-01-17, 3:23 am |
| If you want to track this issue you can file a bug at
http://lab.msdn.microsoft.com/produ...ck/default.aspx . We are driving
hard to ship this week and going through all the bugs filed through msdn is
part of the process.
Thank you for taking the time to work with this driver.
--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
"Wes Clark" < WesClark@discussions
.microsoft.com> wrote in message
news:2E5A6B6D-8FE8-4B6E-BCEC- 96FF45593459@microso
ft.com...
> Do I need to do anything else to report the bug, or do you have the ball
> now?
>
|
|
|
|
|