Remotely Register SQL Server 2005 Express in Enterprise Manager

I have SQL Server 2005 Express installed on a windows server 2003 standard edition. I have configured the windows firewall to allow exceptions for sqlservr.exe and sqlbrowser.exe. I have enabled remote and local connections using tcp/ip & Named Pipes through the Sql Server Surface Area Connection utility. I am able to telnet into the server with: telnet <server ip> <1433>.

Here is the strange part: I am able to connect to the Sql Server remotely from a development machine via Sql Server Management Studio Express, but I am unable to connect to the Sql Server from the same development machine by registering it in Sql Server Enterprise Manager...Is it possible to register SQL Express through Enterprise Manager?

When registering the SQL Express DB in Enterprise Manager I tried both just the (IP Address) of the server and the (IP Address\SQLExpress) as the server/host but I am getting the following errors from enterprise manager:

Just the (IP Address) as the server/host:

Invalid Connection.

ConnectionOpen( Invalid Instance())

(IP Address\SQLEXPRESS) as the server/host:

Sql Server does not exist or access denied. ConnectOpen(Connect()).

Any idea why I can’t register/mount the instance remotely in Enterprise Manager

but I can in Sql Server Management Studio Express?

Thanks,

Greg

You can not use the Enterprise management tools with sql 2005, it is only for sql 2000 and below. The connection layer and management objects are different, but the SQL 2005 Management tools can read the lower version systems. Doing this you will notice that some functions can not be completed on the lower versions due to the differences in connections. For example you can not create a new maintenance plan in the sql 2005 tools to work on sql 2000, but you can edit it after it has been created using the old sql enterprise manager.

Remotely Register SQL Server 2005 Express in Enterprise Manager

I have SQL Server 2005 Express installed on a windows server 2003 standard edition. I have configured the windows firewall to allow exceptions for sqlservr.exe and sqlbrowser.exe. I have enabled remote and local connections using tcp/ip & Named Pipes through the Sql Server Surface Area Connection utility. I am able to telnet into the server with: telnet <server ip> <1433>.

Here is the strange part: I am able to connect to the Sql Server remotely from a development machine via Sql Server Management Studio Express, but I am unable to connect to the Sql Server from the same development machine by registering it in Sql Server Enterprise Manager...Is it possible to register SQL Express through Enterprise Manager?

When registering the SQL Express DB in Enterprise Manager I tried both just the (IP Address) of the server and the (IP Address\SQLExpress) as the server/host but I am getting the following errors from enterprise manager:

Just the (IP Address) as the server/host:

Invalid Connection.

ConnectionOpen( Invalid Instance())

(IP Address\SQLEXPRESS) as the server/host:

Sql Server does not exist or access denied. ConnectOpen(Connect()).

Any idea why I can’t register/mount the instance remotely in Enterprise Manager

but I can in Sql Server Management Studio Express?

Thanks,

Greg

You will not be able to register a sql2k5 on EM. EM uses sql2k dmo which can't be used to manage sql2k5.

You will have to use sql2k5 mgmt tool to manage sqlexpress/sql2k5 instances.|||

Hi OJ,

Thanks for the reply, I was afraid this was going to be the case. Well so here is the follow up to my question.

I am using Sql Server Management Studio Express (SSMSE)...so will I be able to connect/register a sql2k server to SSMSE?

Basically I want to export the data from my sql2K DB into my sql2k5 express DB so will I be able to do that with SSMSE or will I need the full version of Sql Server Management Studio?

Thanks,

Greg

|||I haven't explicitly looked at ssmse. However, I believe it uses smo. Thus, it's capable to connecting to sql2k and manage it.sql

remoted linked server problem

I am running SQL 2005 trigger and update the table in remote linked server. This is working perfectly when the firewall is off. However when I turn on the firewall, it wont run any more. I need the firewall on for the security issue. How can I run this trigger with firewall on? Thanks.Do not create triggers that operate outside the scope of their own database.|||Do not create triggers that operate outside the scope of their own database.

+1

hmscott

Remote server

Use the IP number and PORT instead of the server name. (Of course, that
assumes that the server is availalbe through the firewall and that the port
you indicate is open to the server.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Raul Romero" <rcoronado@.info-arch.com> wrote in message
news:Ooj9pbRnGHA.4816@.TK2MSFTNGP04.phx.gbl...
> Good morning ...
> I need to add a remote server (in another country) to my enterprise
> manager.
> I tried to add it like a local server (inside my network) but doesn't
> work.
> Please, could anybody provide me the steps to do that.
> Thank you for your help,
> Regards,
> Raul.
>Thank you for your answer Arnie ...
I am using IP number to add the servers.
We have two servers in the same city, both servers have a public IP, i tried
to add the servers
in both places (Added server A in B enterprise manager console and B in A
enterprise manager
console), however this only works in one direction, I checked the properties
in both servers and
they are the same, the services running are the same also. Do you have any
idea ?.
Thank you for your help,
Regards,
Raul.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ewFvj$RnGHA.4328@.TK2MSFTNGP04.phx.gbl...
> Use the IP number and PORT instead of the server name. (Of course, that
> assumes that the server is availalbe through the firewall and that the
port
> you indicate is open to the server.)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Raul Romero" <rcoronado@.info-arch.com> wrote in message
> news:Ooj9pbRnGHA.4816@.TK2MSFTNGP04.phx.gbl...
>|||Check that the firewall ports are open to allow traffic in on both sides.
You 'should' add a firewall rule that restricts access to that port to only
the remote server IP.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Raul Romero" <rcoronado@.info-arch.com> wrote in message
news:OVidspSnGHA.2128@.TK2MSFTNGP02.phx.gbl...
> Thank you for your answer Arnie ...
> I am using IP number to add the servers.
> We have two servers in the same city, both servers have a public IP, i
> tried
> to add the servers
> in both places (Added server A in B enterprise manager console and B in A
> enterprise manager
> console), however this only works in one direction, I checked the
> properties
> in both servers and
> they are the same, the services running are the same also. Do you have any
> idea ?.
> Thank you for your help,
> Regards,
> Raul.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ewFvj$RnGHA.4328@.TK2MSFTNGP04.phx.gbl...
> port
>|||Good morning ...
I need to add a remote server (in another country) to my enterprise manager.
I tried to add it like a local server (inside my network) but doesn't work.
Please, could anybody provide me the steps to do that.
Thank you for your help,
Regards,
Raul.|||Use the IP number and PORT instead of the server name. (Of course, that
assumes that the server is availalbe through the firewall and that the port
you indicate is open to the server.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Raul Romero" <rcoronado@.info-arch.com> wrote in message
news:Ooj9pbRnGHA.4816@.TK2MSFTNGP04.phx.gbl...
> Good morning ...
> I need to add a remote server (in another country) to my enterprise
> manager.
> I tried to add it like a local server (inside my network) but doesn't
> work.
> Please, could anybody provide me the steps to do that.
> Thank you for your help,
> Regards,
> Raul.
>|||Thank you for your answer Arnie ...
I am using IP number to add the servers.
We have two servers in the same city, both servers have a public IP, i tried
to add the servers
in both places (Added server A in B enterprise manager console and B in A
enterprise manager
console), however this only works in one direction, I checked the properties
in both servers and
they are the same, the services running are the same also. Do you have any
idea ?.
Thank you for your help,
Regards,
Raul.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ewFvj$RnGHA.4328@.TK2MSFTNGP04.phx.gbl...
> Use the IP number and PORT instead of the server name. (Of course, that
> assumes that the server is availalbe through the firewall and that the
port
> you indicate is open to the server.)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Raul Romero" <rcoronado@.info-arch.com> wrote in message
> news:Ooj9pbRnGHA.4816@.TK2MSFTNGP04.phx.gbl...
>|||Check that the firewall ports are open to allow traffic in on both sides.
You 'should' add a firewall rule that restricts access to that port to only
the remote server IP.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Raul Romero" <rcoronado@.info-arch.com> wrote in message
news:OVidspSnGHA.2128@.TK2MSFTNGP02.phx.gbl...
> Thank you for your answer Arnie ...
> I am using IP number to add the servers.
> We have two servers in the same city, both servers have a public IP, i
> tried
> to add the servers
> in both places (Added server A in B enterprise manager console and B in A
> enterprise manager
> console), however this only works in one direction, I checked the
> properties
> in both servers and
> they are the same, the services running are the same also. Do you have any
> idea ?.
> Thank you for your help,
> Regards,
> Raul.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ewFvj$RnGHA.4328@.TK2MSFTNGP04.phx.gbl...
> port
>

Remote Scripting and Firewall Ports

I need to configure my firewall to allow remote scripting on my SQL 2000 DB.
Which udp and tcp ports do I need to open?Scott (Scott@.discussions.microsoft.com) writes:
> I need to configure my firewall to allow remote scripting on my SQL 2000
> DB. Which udp and tcp ports do I need to open?
Would scripting being any different from running queries?
If SQL Server listens on port 1433, that is your guy.
How remote are these connections? From the Internet? Or just in the
local network? If you expose the server on the Internet, make sure
that you have a really strong password for sa and all other SQL logins for
that matter. And make absolutely sure that you are running at least SP3.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

remote linked server problem

I am running SQL 2005 trigger and update the table in remote linked server.
This is working perfectly when the firewall is off. However when I turn on
the firewall, it won't run any more. I need the firewall on for the security
issue. How can I run this trigger with firewall on? Thanks.Hi
"00ScarlettJohnson" wrote:
> I am running SQL 2005 trigger and update the table in remote linked server.
> This is working perfectly when the firewall is off. However when I turn on
> the firewall, it won't run any more. I need the firewall on for the security
> issue. How can I run this trigger with firewall on? Thanks.
>
You will need to set up specific rules in the firewall to allow this to go
through but not anyone/anywhere else! See
http://support.microsoft.com/kb/287932
John|||After I read the webpage, I am not sure if I understand correctly, I go to
firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
and this is refered to the "any port" from the document. And still didn't
work. Can you please help? I am really not sure if I understand correctly.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1B202C85-C436-4F6A-AA24-2F158E86E03A@.microsoft.com...
> Hi
> "00ScarlettJohnson" wrote:
>> I am running SQL 2005 trigger and update the table in remote linked
>> server.
>> This is working perfectly when the firewall is off. However when I turn
>> on
>> the firewall, it won't run any more. I need the firewall on for the
>> security
>> issue. How can I run this trigger with firewall on? Thanks.
>>
> You will need to set up specific rules in the firewall to allow this to go
> through but not anyone/anywhere else! See
> http://support.microsoft.com/kb/287932
> John|||Hi
"00ScarlettJohnson" wrote:
> After I read the webpage, I am not sure if I understand correctly, I go to
> firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
> and this is refered to the "any port" from the document. And still didn't
> work. Can you please help? I am really not sure if I understand correctly.
> Thanks.
>
Port 1433 is the default port used by SQL Server and is the one registered
for use by IANA. You can configure SQL Server to use a different port through
the Server Networking Utility. If you have not done this it will be using
port 1433 (not 1434 as you specified) unless you have a named instance which
will dynamically assign a port and therefore you will need to change this in
the Server Networking Utility to use a specific port so you can set up a
firewall rule.
Clients will normally dynamically allocate a port to use to communicate with
SQL Server (although you can change this behavour using the Client Network
Utility) but the lowest port number will be 1024, therefore they may use any
port greater than or equal to 1024 to communicate with the server. Each
client can choose a different port.
If you are using the default ports you will need to configure the firewall
rules so that server can received SQL Server requests from any port (>=1024)
on the clients to port 1433 on the server, and the server can return any
communications from port 1433 to any port on the client.
Client Port -> Server Port
Dynamic >= 1024 1433
Server Port -> Client Port
1433 Dynamic Port (same port as above)
HTH
John|||Hi,
I'm just trying to update a table in a remote linked server from a trigger.
The problem is that when the trigger is fired, it gives an error: "No
transaction is active".
I'm using SQL Express 2005 for both servers. The firewall is off in both
machines, also, the MSDTS service is running.
Could you tell me if I'm missing something?
Thanks,
" 00ScarlettJohnson" <EE@.yahoo.com> escribió en el mensaje
news:uTDne%23BpHHA.3736@.TK2MSFTNGP03.phx.gbl...
>I am running SQL 2005 trigger and update the table in remote linked server.
>This is working perfectly when the firewall is off. However when I turn on
>the firewall, it won't run any more. I need the firewall on for the
>security issue. How can I run this trigger with firewall on? Thanks.
>|||Hi
Check that MSDTC is correctly configured
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql
Also make sure that the service accounts for SQL Server is a domain account
http://msdn2.microsoft.com/en-us/library/ms143504.aspx#Use_startup_accounts
If you explicitly start a DISTRIBUTED transaction before the statement that
fires the trigger do you still get the error?
John
"Anahi Ludueña" wrote:
> Hi,
> I'm just trying to update a table in a remote linked server from a trigger.
> The problem is that when the trigger is fired, it gives an error: "No
> transaction is active".
> I'm using SQL Express 2005 for both servers. The firewall is off in both
> machines, also, the MSDTS service is running.
> Could you tell me if I'm missing something?
> Thanks,
>
> " 00ScarlettJohnson" <EE@.yahoo.com> escribió en el mensaje
> news:uTDne%23BpHHA.3736@.TK2MSFTNGP03.phx.gbl...
> >I am running SQL 2005 trigger and update the table in remote linked server.
> >This is working perfectly when the firewall is off. However when I turn on
> >the firewall, it won't run any more. I need the firewall on for the
> >security issue. How can I run this trigger with firewall on? Thanks.
> >
> >
>
>

remote linked server problem

I am running SQL 2005 trigger and update the table in remote linked server.
This is working perfectly when the firewall is off. However when I turn on
the firewall, it won't run any more. I need the firewall on for the security
issue. How can I run this trigger with firewall on? Thanks.Hi
"00ScarlettJohnson" wrote:

> I am running SQL 2005 trigger and update the table in remote linked server
.
> This is working perfectly when the firewall is off. However when I turn on
> the firewall, it won't run any more. I need the firewall on for the securi
ty
> issue. How can I run this trigger with firewall on? Thanks.
>
You will need to set up specific rules in the firewall to allow this to go
through but not anyone/anywhere else! See
http://support.microsoft.com/kb/287932
John|||After I read the webpage, I am not sure if I understand correctly, I go to
firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
and this is refered to the "any port" from the document. And still didn't
work. Can you please help? I am really not sure if I understand correctly.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1B202C85-C436-4F6A-AA24-2F158E86E03A@.microsoft.com...
> Hi
> "00ScarlettJohnson" wrote:
>
> You will need to set up specific rules in the firewall to allow this to go
> through but not anyone/anywhere else! See
> http://support.microsoft.com/kb/287932
> John|||Hi
"00ScarlettJohnson" wrote:

> After I read the webpage, I am not sure if I understand correctly, I go t
o
> firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
> and this is refered to the "any port" from the document. And still didn't
> work. Can you please help? I am really not sure if I understand correctly
.
> Thanks.
>
Port 1433 is the default port used by SQL Server and is the one registered
for use by IANA. You can configure SQL Server to use a different port throug
h
the Server Networking Utility. If you have not done this it will be using
port 1433 (not 1434 as you specified) unless you have a named instance which
will dynamically assign a port and therefore you will need to change this in
the Server Networking Utility to use a specific port so you can set up a
firewall rule.
Clients will normally dynamically allocate a port to use to communicate with
SQL Server (although you can change this behavour using the Client Network
Utility) but the lowest port number will be 1024, therefore they may use any
port greater than or equal to 1024 to communicate with the server. Each
client can choose a different port.
If you are using the default ports you will need to configure the firewall
rules so that server can received SQL Server requests from any port (>=1024)
on the clients to port 1433 on the server, and the server can return any
communications from port 1433 to any port on the client.
Client Port -> Server Port
Dynamic >= 1024 1433
Server Port -> Client Port
1433 Dynamic Port (same port as above)
HTH
John

remote linked server problem

I am running SQL 2005 trigger and update the table in remote linked server.
This is working perfectly when the firewall is off. However when I turn on
the firewall, it won't run any more. I need the firewall on for the security
issue. How can I run this trigger with firewall on? Thanks.
Hi
"00ScarlettJohnson" wrote:

> I am running SQL 2005 trigger and update the table in remote linked server.
> This is working perfectly when the firewall is off. However when I turn on
> the firewall, it won't run any more. I need the firewall on for the security
> issue. How can I run this trigger with firewall on? Thanks.
>
You will need to set up specific rules in the firewall to allow this to go
through but not anyone/anywhere else! See
http://support.microsoft.com/kb/287932
John
|||After I read the webpage, I am not sure if I understand correctly, I go to
firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
and this is refered to the "any port" from the document. And still didn't
work. Can you please help? I am really not sure if I understand correctly.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1B202C85-C436-4F6A-AA24-2F158E86E03A@.microsoft.com...
> Hi
> "00ScarlettJohnson" wrote:
> You will need to set up specific rules in the firewall to allow this to go
> through but not anyone/anywhere else! See
> http://support.microsoft.com/kb/287932
> John
|||Hi
"00ScarlettJohnson" wrote:

> After I read the webpage, I am not sure if I understand correctly, I go to
> firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
> and this is refered to the "any port" from the document. And still didn't
> work. Can you please help? I am really not sure if I understand correctly.
> Thanks.
>
Port 1433 is the default port used by SQL Server and is the one registered
for use by IANA. You can configure SQL Server to use a different port through
the Server Networking Utility. If you have not done this it will be using
port 1433 (not 1434 as you specified) unless you have a named instance which
will dynamically assign a port and therefore you will need to change this in
the Server Networking Utility to use a specific port so you can set up a
firewall rule.
Clients will normally dynamically allocate a port to use to communicate with
SQL Server (although you can change this behavour using the Client Network
Utility) but the lowest port number will be 1024, therefore they may use any
port greater than or equal to 1024 to communicate with the server. Each
client can choose a different port.
If you are using the default ports you will need to configure the firewall
rules so that server can received SQL Server requests from any port (>=1024)
on the clients to port 1433 on the server, and the server can return any
communications from port 1433 to any port on the client.
Client Port -> Server Port
Dynamic >= 1024 1433
Server Port -> Client Port
1433 Dynamic Port (same port as above)
HTH
John