We have a client that would like to have their (Remote) web server
SQL2K instance set-up for 2-way replication against their internal
private SQL2K instance (Local). The problem is their network security
department won't allow *any* TCP/IP socket connections to be
initiated from the Remote SQL2K server through the firewall to their
internal Local server. However outbound connections, initiated from the
Local server (i.e. Local -> Firewall -> Remote), are allowed.
Can SQL Server support 2-way replication under this sort of firewall
restriction?
I imagine the Remote web server SQL2K instance would need to be
configured as the Publisher (listening on port 1433) and the Local
SQL2K instance a Subscriber. The Local Subscriber would then be
configured to connect periodically to the Publisher for updates. When
the Subscriber is connected all queued data deltas (on either server)
would be replicated between them to insure both instances are
synchronized. The Subscriber would then disconnect. (or not?)
Would the distributor process need to run on the Remote or Local
server? Would we need to run merge or transactional based replication?
More info:
- The network connection between servers should hardly
ever be down.
- Not all tables in the database schema need to be
replicated, just a subset.
- To begin with the tables will be empty on both servers,
so we don't need to worry about an initial snapshot.
- We hope to use SQL2K's Auto Identity Range Management
to ensure unique keys do not collide.
Thanks for any feedback you can offer.
Good to hear that it can work Markus,
Do you see any problems in running a very short period between data
merge pushes? Say every minute or even less?
Will SQL2K prevent a new merge from running if one is already in
progress?
-Paul
Showing posts with label remote. Show all posts
Showing posts with label remote. Show all posts
Monday, March 19, 2012
Friday, February 24, 2012
2005 SP1 (remote Reporting Services upgrade failures)
Why can it never be easy? I applied SP1 to one of our 2005 database servers and upgraded all components successfully. However when trying to install on a stand alone Reporting Services instance that uses the above database server I can't get past the Login verification. Windows or Sql...Per the ReadMe I am using an acccount that is administrator on both servers and sysadmin in sql, also am running the exe as same admin account.
There are no logs under %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files but I found this error in the hotfix log.
[SQL Native Client]TCP Provider: The requested address is not valid in
its context.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections.
Verified that the SQL Server allows remote connections (TCP\IP is enabled, Named Pipes is not); even started the Browser sevice that hasn't been running and still get the same error.
Will the steps outlined by Lukasz at http://blogs.msdn.com/lukaszp/archive/2006/05/18/601340.aspx solve my problem or will I still need to upgrade the RS files somehow? I think I belong to 'Sometimes does not work" #3. The Native Client upgrade runs fine by itself.
Advice and commiserations appreciated.
AB
Solved error above by recreating the client alias to the sql server. The port designation had disappeared even though it was default port.
Now am experiencing locked file errors - SQL Reporting Services service is disabled and the web site is stopped. What other services need to be stopped? Do services on the remote sql server also need to be stopped?
AB
Sunday, February 19, 2012
2005 remote connection problem (yes, I have enabled TCP/IP)
I've gone through about 30+ google results looking for the magic answer to
this problem. I have a brand new machine with a brand new install of Sql
Server 2005 express. I am trying to connect via SQLCMD in a command window
with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
the ip is my local machine and the machine that SS2005 Express is installed
on.
-U is a login that I created. I have set authentication to mixed mode
Not sure what else to try. Anyone have any hints?"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
>
By default SqlExpress uses an instance name of SQLEXPRESS. Your connection
info would be correct for the default instance, not a named instance.
Try
SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
Also to user the instance name you need the SQL Browser service started.
And double check in Computer Management\SQL Server Configuration
Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
David|||Have you enabled remote connections? Yes, you have to explicitly enable it
to accept connections from external machines. Doesn't matter what libraries
or connection strings you are sending. If remote connections are not
enabled, the only thing that any SQL Server 2005 database will respond to is
something from the machine it resides on.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
> Not sure what else to try. Anyone have any hints?
>|||Hi Michael,
I have enabled them the only way I know how, what I did was use "SQL Server
Surface Are Configuration" app and checked the "Using TCP/IP only" option
under the "Local and remote connections" section. This is one of the first
things that I did. I should have mentioned that in my original post,
apologies.
Any other ideas?
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u%23XwyS0NGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Have you enabled remote connections? Yes, you have to explicitly enable
> it to accept connections from external machines. Doesn't matter what
> libraries or connection strings you are sending. If remote connections
> are not enabled, the only thing that any SQL Server 2005 database will
> respond to is something from the machine it resides on.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
>> I've gone through about 30+ google results looking for the magic answer
>> to this problem. I have a brand new machine with a brand new install of
>> Sql Server 2005 express. I am trying to connect via SQLCMD in a command
>> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P
>> ******!
>> the ip is my local machine and the machine that SS2005 Express is
>> installed on.
>> -U is a login that I created. I have set authentication to mixed mode
>> Not sure what else to try. Anyone have any hints?
>>
>|||Hi David,
I have tried to specify the name in the address, it doesn't make a
difference I'm afraid.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OmHC9Q0NGHA.2668@.tk2msftngp13.phx.gbl...
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
>> I've gone through about 30+ google results looking for the magic answer
>> to this problem. I have a brand new machine with a brand new install of
>> Sql Server 2005 express. I am trying to connect via SQLCMD in a command
>> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P
>> ******!
>> the ip is my local machine and the machine that SS2005 Express is
>> installed on.
>> -U is a login that I created. I have set authentication to mixed mode
> By default SqlExpress uses an instance name of SQLEXPRESS. Your
> connection info would be correct for the default instance, not a named
> instance.
> Try
> SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
>
> How to: Connect to the Database Engine Using sqlcmd.exe
> http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
> Also to user the instance name you need the SQL Browser service started.
> And double check in Computer Management\SQL Server Configuration
> Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
> David
>|||why I can't remember this after 15 years using a computer: "When things
don't seem right, reboot"
Sure enough, although I restarted SQL Server several times, apparently it
took a full reboot to make things operate correctly.
Thanks for the suggestions guys, I'm up and running now :)
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
> Not sure what else to try. Anyone have any hints?
>
this problem. I have a brand new machine with a brand new install of Sql
Server 2005 express. I am trying to connect via SQLCMD in a command window
with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
the ip is my local machine and the machine that SS2005 Express is installed
on.
-U is a login that I created. I have set authentication to mixed mode
Not sure what else to try. Anyone have any hints?"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
>
By default SqlExpress uses an instance name of SQLEXPRESS. Your connection
info would be correct for the default instance, not a named instance.
Try
SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
Also to user the instance name you need the SQL Browser service started.
And double check in Computer Management\SQL Server Configuration
Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
David|||Have you enabled remote connections? Yes, you have to explicitly enable it
to accept connections from external machines. Doesn't matter what libraries
or connection strings you are sending. If remote connections are not
enabled, the only thing that any SQL Server 2005 database will respond to is
something from the machine it resides on.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
> Not sure what else to try. Anyone have any hints?
>|||Hi Michael,
I have enabled them the only way I know how, what I did was use "SQL Server
Surface Are Configuration" app and checked the "Using TCP/IP only" option
under the "Local and remote connections" section. This is one of the first
things that I did. I should have mentioned that in my original post,
apologies.
Any other ideas?
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u%23XwyS0NGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Have you enabled remote connections? Yes, you have to explicitly enable
> it to accept connections from external machines. Doesn't matter what
> libraries or connection strings you are sending. If remote connections
> are not enabled, the only thing that any SQL Server 2005 database will
> respond to is something from the machine it resides on.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
>> I've gone through about 30+ google results looking for the magic answer
>> to this problem. I have a brand new machine with a brand new install of
>> Sql Server 2005 express. I am trying to connect via SQLCMD in a command
>> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P
>> ******!
>> the ip is my local machine and the machine that SS2005 Express is
>> installed on.
>> -U is a login that I created. I have set authentication to mixed mode
>> Not sure what else to try. Anyone have any hints?
>>
>|||Hi David,
I have tried to specify the name in the address, it doesn't make a
difference I'm afraid.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OmHC9Q0NGHA.2668@.tk2msftngp13.phx.gbl...
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
>> I've gone through about 30+ google results looking for the magic answer
>> to this problem. I have a brand new machine with a brand new install of
>> Sql Server 2005 express. I am trying to connect via SQLCMD in a command
>> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P
>> ******!
>> the ip is my local machine and the machine that SS2005 Express is
>> installed on.
>> -U is a login that I created. I have set authentication to mixed mode
> By default SqlExpress uses an instance name of SQLEXPRESS. Your
> connection info would be correct for the default instance, not a named
> instance.
> Try
> SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
>
> How to: Connect to the Database Engine Using sqlcmd.exe
> http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
> Also to user the instance name you need the SQL Browser service started.
> And double check in Computer Management\SQL Server Configuration
> Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
> David
>|||why I can't remember this after 15 years using a computer: "When things
don't seem right, reboot"
Sure enough, although I restarted SQL Server several times, apparently it
took a full reboot to make things operate correctly.
Thanks for the suggestions guys, I'm up and running now :)
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
> Not sure what else to try. Anyone have any hints?
>
2005 remote connection problem (yes, I have enabled TCP/IP)
I've gone through about 30+ google results looking for the magic answer to
this problem. I have a brand new machine with a brand new install of Sql
Server 2005 express. I am trying to connect via SQLCMD in a command window
with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
the ip is my local machine and the machine that SS2005 Express is installed
on.
-U is a login that I created. I have set authentication to mixed mode
Not sure what else to try. Anyone have any hints?
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
>
By default SqlExpress uses an instance name of SQLEXPRESS. Your connection
info would be correct for the default instance, not a named instance.
Try
SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
Also to user the instance name you need the SQL Browser service started.
And double check in Computer Management\SQL Server Configuration
Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
David
|||Have you enabled remote connections? Yes, you have to explicitly enable it
to accept connections from external machines. Doesn't matter what libraries
or connection strings you are sending. If remote connections are not
enabled, the only thing that any SQL Server 2005 database will respond to is
something from the machine it resides on.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
> Not sure what else to try. Anyone have any hints?
>
|||Hi Michael,
I have enabled them the only way I know how, what I did was use "SQL Server
Surface Are Configuration" app and checked the "Using TCP/IP only" option
under the "Local and remote connections" section. This is one of the first
things that I did. I should have mentioned that in my original post,
apologies.
Any other ideas?
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u%23XwyS0NGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Have you enabled remote connections? Yes, you have to explicitly enable
> it to accept connections from external machines. Doesn't matter what
> libraries or connection strings you are sending. If remote connections
> are not enabled, the only thing that any SQL Server 2005 database will
> respond to is something from the machine it resides on.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
>
|||Hi David,
I have tried to specify the name in the address, it doesn't make a
difference I'm afraid.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OmHC9Q0NGHA.2668@.tk2msftngp13.phx.gbl...
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> By default SqlExpress uses an instance name of SQLEXPRESS. Your
> connection info would be correct for the default instance, not a named
> instance.
> Try
> SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
>
> How to: Connect to the Database Engine Using sqlcmd.exe
> http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
> Also to user the instance name you need the SQL Browser service started.
> And double check in Computer Management\SQL Server Configuration
> Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
> David
>
this problem. I have a brand new machine with a brand new install of Sql
Server 2005 express. I am trying to connect via SQLCMD in a command window
with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
the ip is my local machine and the machine that SS2005 Express is installed
on.
-U is a login that I created. I have set authentication to mixed mode
Not sure what else to try. Anyone have any hints?
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
>
By default SqlExpress uses an instance name of SQLEXPRESS. Your connection
info would be correct for the default instance, not a named instance.
Try
SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
Also to user the instance name you need the SQL Browser service started.
And double check in Computer Management\SQL Server Configuration
Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
David
|||Have you enabled remote connections? Yes, you have to explicitly enable it
to accept connections from external machines. Doesn't matter what libraries
or connection strings you are sending. If remote connections are not
enabled, the only thing that any SQL Server 2005 database will respond to is
something from the machine it resides on.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
> Not sure what else to try. Anyone have any hints?
>
|||Hi Michael,
I have enabled them the only way I know how, what I did was use "SQL Server
Surface Are Configuration" app and checked the "Using TCP/IP only" option
under the "Local and remote connections" section. This is one of the first
things that I did. I should have mentioned that in my original post,
apologies.
Any other ideas?
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u%23XwyS0NGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Have you enabled remote connections? Yes, you have to explicitly enable
> it to accept connections from external machines. Doesn't matter what
> libraries or connection strings you are sending. If remote connections
> are not enabled, the only thing that any SQL Server 2005 database will
> respond to is something from the machine it resides on.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
>
|||Hi David,
I have tried to specify the name in the address, it doesn't make a
difference I'm afraid.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OmHC9Q0NGHA.2668@.tk2msftngp13.phx.gbl...
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> By default SqlExpress uses an instance name of SQLEXPRESS. Your
> connection info would be correct for the default instance, not a named
> instance.
> Try
> SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
>
> How to: Connect to the Database Engine Using sqlcmd.exe
> http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
> Also to user the instance name you need the SQL Browser service started.
> And double check in Computer Management\SQL Server Configuration
> Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
> David
>
2005 remote connection problem (yes, I have enabled TCP/IP)
I've gone through about 30+ google results looking for the magic answer to
this problem. I have a brand new machine with a brand new install of Sql
Server 2005 express. I am trying to connect via SQLCMD in a command window
with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
the ip is my local machine and the machine that SS2005 Express is installed
on.
-U is a login that I created. I have set authentication to mixed mode
Not sure what else to try. Anyone have any hints?"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
>
By default SqlExpress uses an instance name of SQLEXPRESS. Your connection
info would be correct for the default instance, not a named instance.
Try
SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
Also to user the instance name you need the SQL Browser service started.
And double check in Computer Management\SQL Server Configuration
Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
David|||Have you enabled remote connections? Yes, you have to explicitly enable it
to accept connections from external machines. Doesn't matter what libraries
or connection strings you are sending. If remote connections are not
enabled, the only thing that any SQL Server 2005 database will respond to is
something from the machine it resides on.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
> Not sure what else to try. Anyone have any hints?
>|||Hi Michael,
I have enabled them the only way I know how, what I did was use "SQL Server
Surface Are Configuration" app and checked the "Using TCP/IP only" option
under the "Local and remote connections" section. This is one of the first
things that I did. I should have mentioned that in my original post,
apologies.
Any other ideas?
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u%23XwyS0NGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Have you enabled remote connections? Yes, you have to explicitly enable
> it to accept connections from external machines. Doesn't matter what
> libraries or connection strings you are sending. If remote connections
> are not enabled, the only thing that any SQL Server 2005 database will
> respond to is something from the machine it resides on.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
>|||Hi David,
I have tried to specify the name in the address, it doesn't make a
difference I'm afraid.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OmHC9Q0NGHA.2668@.tk2msftngp13.phx.gbl...
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> By default SqlExpress uses an instance name of SQLEXPRESS. Your
> connection info would be correct for the default instance, not a named
> instance.
> Try
> SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
>
> How to: Connect to the Database Engine Using sqlcmd.exe
> http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
> Also to user the instance name you need the SQL Browser service started.
> And double check in Computer Management\SQL Server Configuration
> Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
> David
>
this problem. I have a brand new machine with a brand new install of Sql
Server 2005 express. I am trying to connect via SQLCMD in a command window
with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
the ip is my local machine and the machine that SS2005 Express is installed
on.
-U is a login that I created. I have set authentication to mixed mode
Not sure what else to try. Anyone have any hints?"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
>
By default SqlExpress uses an instance name of SQLEXPRESS. Your connection
info would be correct for the default instance, not a named instance.
Try
SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
Also to user the instance name you need the SQL Browser service started.
And double check in Computer Management\SQL Server Configuration
Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
David|||Have you enabled remote connections? Yes, you have to explicitly enable it
to accept connections from external machines. Doesn't matter what libraries
or connection strings you are sending. If remote connections are not
enabled, the only thing that any SQL Server 2005 database will respond to is
something from the machine it resides on.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
> Not sure what else to try. Anyone have any hints?
>|||Hi Michael,
I have enabled them the only way I know how, what I did was use "SQL Server
Surface Are Configuration" app and checked the "Using TCP/IP only" option
under the "Local and remote connections" section. This is one of the first
things that I did. I should have mentioned that in my original post,
apologies.
Any other ideas?
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u%23XwyS0NGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Have you enabled remote connections? Yes, you have to explicitly enable
> it to accept connections from external machines. Doesn't matter what
> libraries or connection strings you are sending. If remote connections
> are not enabled, the only thing that any SQL Server 2005 database will
> respond to is something from the machine it resides on.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
>|||Hi David,
I have tried to specify the name in the address, it doesn't make a
difference I'm afraid.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OmHC9Q0NGHA.2668@.tk2msftngp13.phx.gbl...
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> By default SqlExpress uses an instance name of SQLEXPRESS. Your
> connection info would be correct for the default instance, not a named
> instance.
> Try
> SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
>
> How to: Connect to the Database Engine Using sqlcmd.exe
> http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
> Also to user the instance name you need the SQL Browser service started.
> And double check in Computer Management\SQL Server Configuration
> Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
> David
>
2005 Remote Connection Error
Hi,
Hope someone can help with this, having a huge problem connecting to SQL 2005 from a client machine. I get the following error:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)"
I have SQL 2005 installed on a server containing SQL 2000 (default instance) and then SQL 2005 as the instance SERVER\SQL2005. 2005 is running SP1. I'm connecting via windows authentication, using just using Management Studio. I've also configured the server to accept remote connections.
I can connect locally to the 2005 instance. Does anyone have any ideas why this might be happening?
Thanks
Jeremy
See my Screencast about Remote connections on my site which describes in detail how to enable remote connections.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Thanks for the reply Jens,
I have definitely enable remote connections already. Do you have any other ideas?
Thanks
Jeremy
|||Can anyone help with this?|||Sorry for not coming back earlier, lsot the focus on the post. DO you have SQL Server Browser started on the server ? SQL Browser will redirect the client request to connect to the assigned port without naming explictly the port of the SQL Server. If you don′t start the browser on the server, the client will try to connect to the port 1433 which might not be used by the named instance. If you know the port of the named instance, you can also specify that directly within the connection string using servername\instanceName,Portnumber.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Monday, February 13, 2012
2005 Express Error: Could not open a connection to SQL Server
Hi, I can't connect to my remote SQL Server 2005 Express - can anyone
help?
SETTINGS:
Server Type: Database Engine
Server Name: {my remote IP}
Authentication: SQL Authentication
User Name: {my Username}
Password: {my Password}
ERROR:
Error: Testing the registered server failed. Verify the server name,
login credentials, and database, and then click test again.
Additional Informtion: An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: Named Piper
Provider, error:40 - Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 53)
TROUBLESHOOTING:
- Windows firewall is not enabled on the server.
- When I test "telnet {my remote IP} 1433", i get "Could not open
connection to the host, on port 1433: Connect Failed".
- When pinging my ip, I get 0% loss.
- I have established via the SQL Server Surface Area Configuration
tool that Local and remote connections, using both TCP/IP and named
pipes, is enabled.
- The SQL Server Browser service is started.
Cheers.
Sorry, I also established through the SQL Server Configuration Manager
> SQL Server 2005 Network Configuration > Protocols for MSSQLSERVER
that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
server IP ?) is set to 1433,2433.
|||Do you have \SQLEXPRESS on the end of the server name? Is the SQL Browser
running on the server where SQL Express is running?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"iporter" <isporter@.gmail.com> wrote in message
news:1175251815.059956.99630@.o5g2000hsb.googlegrou ps.com...
> Sorry, I also established through the SQL Server Configuration Manager
> that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
> Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
> server IP ?) is set to 1433,2433.
>
help?
SETTINGS:
Server Type: Database Engine
Server Name: {my remote IP}
Authentication: SQL Authentication
User Name: {my Username}
Password: {my Password}
ERROR:
Error: Testing the registered server failed. Verify the server name,
login credentials, and database, and then click test again.
Additional Informtion: An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: Named Piper
Provider, error:40 - Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 53)
TROUBLESHOOTING:
- Windows firewall is not enabled on the server.
- When I test "telnet {my remote IP} 1433", i get "Could not open
connection to the host, on port 1433: Connect Failed".
- When pinging my ip, I get 0% loss.
- I have established via the SQL Server Surface Area Configuration
tool that Local and remote connections, using both TCP/IP and named
pipes, is enabled.
- The SQL Server Browser service is started.
Cheers.
Sorry, I also established through the SQL Server Configuration Manager
> SQL Server 2005 Network Configuration > Protocols for MSSQLSERVER
that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
server IP ?) is set to 1433,2433.
|||Do you have \SQLEXPRESS on the end of the server name? Is the SQL Browser
running on the server where SQL Express is running?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"iporter" <isporter@.gmail.com> wrote in message
news:1175251815.059956.99630@.o5g2000hsb.googlegrou ps.com...
> Sorry, I also established through the SQL Server Configuration Manager
> that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
> Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
> server IP ?) is set to 1433,2433.
>
2005 Express Error: Could not open a connection to SQL Server
Hi, I can't connect to my remote SQL Server 2005 Express - can anyone
help?
SETTINGS:
Server Type: Database Engine
Server Name: {my remote IP}
Authentication: SQL Authentication
User Name: {my Username}
Password: {my Password}
ERROR:
Error: Testing the registered server failed. Verify the server name,
login credentials, and database, and then click test again.
Additional Informtion: An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: Named Piper
Provider, error:40 - Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 53)
TROUBLESHOOTING:
- Windows firewall is not enabled on the server.
- When I test "telnet {my remote IP} 1433", i get "Could not open
connection to the host, on port 1433: Connect Failed".
- When pinging my ip, I get 0% loss.
- I have established via the SQL Server Surface Area Configuration
tool that Local and remote connections, using both TCP/IP and named
pipes, is enabled.
- The SQL Server Browser service is started.
Cheers.Sorry, I also established through the SQL Server Configuration Manager
> SQL Server 2005 Network Configuration > Protocols for MSSQLSERVER
that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
server IP ?) is set to 1433,2433.|||Do you have \SQLEXPRESS on the end of the server name? Is the SQL Browser
running on the server where SQL Express is running?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"iporter" <isporter@.gmail.com> wrote in message
news:1175251815.059956.99630@.o5g2000hsb.googlegroups.com...
> Sorry, I also established through the SQL Server Configuration Manager
> that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
> Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
> server IP ?) is set to 1433,2433.
>
help?
SETTINGS:
Server Type: Database Engine
Server Name: {my remote IP}
Authentication: SQL Authentication
User Name: {my Username}
Password: {my Password}
ERROR:
Error: Testing the registered server failed. Verify the server name,
login credentials, and database, and then click test again.
Additional Informtion: An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: Named Piper
Provider, error:40 - Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 53)
TROUBLESHOOTING:
- Windows firewall is not enabled on the server.
- When I test "telnet {my remote IP} 1433", i get "Could not open
connection to the host, on port 1433: Connect Failed".
- When pinging my ip, I get 0% loss.
- I have established via the SQL Server Surface Area Configuration
tool that Local and remote connections, using both TCP/IP and named
pipes, is enabled.
- The SQL Server Browser service is started.
Cheers.Sorry, I also established through the SQL Server Configuration Manager
> SQL Server 2005 Network Configuration > Protocols for MSSQLSERVER
that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
server IP ?) is set to 1433,2433.|||Do you have \SQLEXPRESS on the end of the server name? Is the SQL Browser
running on the server where SQL Express is running?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"iporter" <isporter@.gmail.com> wrote in message
news:1175251815.059956.99630@.o5g2000hsb.googlegroups.com...
> Sorry, I also established through the SQL Server Configuration Manager
> that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
> Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
> server IP ?) is set to 1433,2433.
>
2005 Express and Replication
So if I have a remote location running Express that needs to publish data to
a central server, Enterprise edition, can I do that or is it not possible
due to Express being a Subscriber only for Merge and Transactional
Replication?
If this is not possible, what would be the best way to handle the publishing
of data from the remote to the central location taken into account the fact
that the remote is on a dial-up internet connection only available during
off-hours?
TIA
Michael MacGregor
Database Architect
I'd set it up as a merge subscriber - this allows for offline access and
will send data back to the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
a central server, Enterprise edition, can I do that or is it not possible
due to Express being a Subscriber only for Merge and Transactional
Replication?
If this is not possible, what would be the best way to handle the publishing
of data from the remote to the central location taken into account the fact
that the remote is on a dial-up internet connection only available during
off-hours?
TIA
Michael MacGregor
Database Architect
I'd set it up as a merge subscriber - this allows for offline access and
will send data back to the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Subscribe to:
Posts (Atom)