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

Remote-Agent Activation

Hi,
Two questions:
1. Not 100-percent sure about this, but it looks like for a
subscription configured with remote agent activation where the
destination owner for articles are not specified as dbo, during a
resync the articles at the subscriber can be owned by any login on the
subscriber. Is this correct? If so, how can this be fixed without
reinitialising the subscriptions!
2. Is it possible with a no-sync subscription to progate schema changes
without recreating the subscription?
Thanks in advance for any worthwhile comments.
1) no, articles define to have a different object owner on the subscriber
should always be owned by the different object owner after a resync.
2) yes, in sql 2005 the default is to replicate ddl changes, in sql 2000 you
can use sp_repladdcolumn or sp_repldropcolumn. It doesn't matter if you did
an automatic sync or a no sync.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<paul.mu@.gapbuster.com> wrote in message
news:1159942193.130907.275300@.h48g2000cwc.googlegr oups.com...
> Hi,
> Two questions:
> 1. Not 100-percent sure about this, but it looks like for a
> subscription configured with remote agent activation where the
> destination owner for articles are not specified as dbo, during a
> resync the articles at the subscriber can be owned by any login on the
> subscriber. Is this correct? If so, how can this be fixed without
> reinitialising the subscriptions!
> 2. Is it possible with a no-sync subscription to progate schema changes
> without recreating the subscription?
> Thanks in advance for any worthwhile comments.
>
|||Thanks for your response Hilary.
The answer to my first question was not what I was after.
Firstly, I am working with transactional replication on SQL Server
2000.
Secondly, I did not have this issue before he change to use remote
agent activation.
The current setup is that most of the articles in the publications do
not have the 'destination_owner' set (it is left as a blank) - the
source_owner is always 'dbo'.
The problem: with remote agent activation, when performing a resync,
the articles at the subscribers have been created with a different
'destination_owner' which is not 'dbo'. This owner can vary from one
resync to another.
A question: can this issue be resolved without having to reconfigure
the publication (ie. replace all blank destination_owner with 'dbo'),
which will require a resync?

remote webwork place

We have a SBS2003 server. And configured remote workplace to allow remote users

access to their workstion. On the windows XP Pro sp2 station I have added the user to the "Remote desktop" list ( control panel-system-remote-allow users to remotely connect this computer - Add .)

Nomal users can pass router and select wanted station then get logon screen. But this error message is showed up:

"The local policy of this system does not permit you to logon interractively."

The administrator can logon to any station remotely. Do I have to give additional permission to normal user?

Thank you for any help or clue.

Peter

THis seems more a Windows problem than a SQL Server (Express) problem. You might try reaching someone on the public newsgroups covering SBS 2003.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Peter,

Control Panel -> Administrative Tools -> Local Security Policy -> Local Policy -> User Rights Assignments -> Log On Locally

@. here you have to add the Domain Users you wish to grant log on to your system (Remote Server)

Regards

Hemantgiri S. Goswami

|||

Hemantgiri,

Thank you very much. It solved my problem.

Peter

remote transaction on SQL Server

To support remote transaction on SQL Server i have configured user
options to 16384. However if i make changes in other properties of SQL
Server, user option setting reverts to its original value. I would
like to know 1. Is it possible to protect this setting so that change
in other properties do not effect this setting? 2. What are the
properties of SQL Server that are linked with user option settings
(i.e. Changes in those properties cause reverting in user options)?> However if i make changes in other properties of SQL
> Server, user option setting reverts to its original value. I would
> like to know 1. Is it possible to protect this setting so that change
> in other properties do not effect this setting?

The 'user options' configuration option is a bitmask specification. You
need to perform a bitwise OR in order to leave the other options intact.
The script below will turn on the specified option on and retain the other
option settings.

DECLARE
@.run_value int,
@.new_run_value int
CREATE TABLE #UserOptions
(
name varchar(40) NOT NULL,
minimun int,
maximmun int,
config_value int,
run_value int
)
INSERT INTO #UserOptions
EXEC sp_configure 'user options'

SELECT @.new_run_value = run_value | 16384
FROM #UserOptions

EXEC sp_configure 'user options', @.new_run_value
RECONFIGURE WITH OVERRIDE
DROP TABLE #UserOptions
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"T.S.Negi" <tilak.negi@.mind-infotech.com> wrote in message
news:a1930058.0402152114.6d399d0c@.posting.google.c om...
> To support remote transaction on SQL Server i have configured user
> options to 16384. However if i make changes in other properties of SQL
> Server, user option setting reverts to its original value. I would
> like to know 1. Is it possible to protect this setting so that change
> in other properties do not effect this setting? 2. What are the
> properties of SQL Server that are linked with user option settings
> (i.e. Changes in those properties cause reverting in user options)?

Remote Server connect error

Hi there.
I'm getting the following error when trying to connect to a Remote Server
I've set up:
Server 'Server_1' is not configured for DATA ACCESS.
I know this option is available with Linked Servers but I dont know what to
do here. I have RPC enabled and valid server and login names defined in the
Remote Server settings.
any ideas?
Try executing:
exec sp_serveroption 'Server_1', 'data access', 'true'
-Sue
On Wed, 30 Mar 2005 09:51:05 -0800, len
<len@.discussions.microsoft.com> wrote:

>Hi there.
>I'm getting the following error when trying to connect to a Remote Server
>I've set up:
>Server 'Server_1' is not configured for DATA ACCESS.
>I know this option is available with Linked Servers but I dont know what to
>do here. I have RPC enabled and valid server and login names defined in the
>Remote Server settings.
>any ideas?

Remote Server connect error

Hi there.
I'm getting the following error when trying to connect to a Remote Server
I've set up:
Server 'Server_1' is not configured for DATA ACCESS.
I know this option is available with Linked Servers but I dont know what to
do here. I have RPC enabled and valid server and login names defined in the
Remote Server settings.
any ideas?Try executing:
exec sp_serveroption 'Server_1', 'data access', 'true'
-Sue
On Wed, 30 Mar 2005 09:51:05 -0800, len
<len@.discussions.microsoft.com> wrote:

>Hi there.
>I'm getting the following error when trying to connect to a Remote Server
>I've set up:
>Server 'Server_1' is not configured for DATA ACCESS.
>I know this option is available with Linked Servers but I dont know what to
>do here. I have RPC enabled and valid server and login names defined in the
>Remote Server settings.
>any ideas?

Remote query error

Hello, when I try a remote query in Query Analyzer from
serverA I am getting an error: Server 'serverB' is not
configured for DATA ACCESS.
ServerB is not defined as a Linked Server as it is already
defined as a remote server (it participates in
replication). I can run queries on it directly,
applications can access it, etc. Concurrent access is set
at unlimited.
I am using SQL 7
Any help is greatly appreciated.
RicSee sp_serveroption in BOL.
>--Original Message--
>Hello, when I try a remote query in Query Analyzer from
>serverA I am getting an error: Server 'serverB' is not
>configured for DATA ACCESS.
>ServerB is not defined as a Linked Server as it is
already
>defined as a remote server (it participates in
>replication). I can run queries on it directly,
>applications can access it, etc. Concurrent access is
set
>at unlimited.
>I am using SQL 7
>Any help is greatly appreciated.
>Ric
>.
>|||I put in the the sp_serveroption and gave it data access
but now i get this...Im using a remote server
connection...what am I doing wrong?
Login failed for user '(null)'. Reason: Not associated
with a trusted SQL Server connection.|||Many thanks Chris, that worked perfectly.
Ric
>--Original Message--
>See sp_serveroption in BOL.
>>--Original Message--
>>Hello, when I try a remote query in Query Analyzer from
>>serverA I am getting an error: Server 'serverB' is not
>>configured for DATA ACCESS.
>>ServerB is not defined as a Linked Server as it is
>already
>>defined as a remote server (it participates in
>>replication). I can run queries on it directly,
>>applications can access it, etc. Concurrent access is
>set
>>at unlimited.
>>I am using SQL 7
>>Any help is greatly appreciated.
>>Ric
>>.
>.
>|||I did this and gave it data access but now I get the error
that the login for user null failed...not associated with
a trusted SQL Server connection

Remote Distributor and Subscriber on same machine - is that OK?

Hi,

Server A houses the Production DB which services an OLTP system. Transactional replication is configured on this server which has two subscribers - on two separate servers (servers B and C). The Distribution database currently resides on Server A (which is also the Publisher).

Server B is on the same LAN as Server A (on the same rack). I'm considering moving the Distribution database from Server A to Server B to offload some of the processing overhead from Server A.

Are there any gotchas/performance problems associated with having the Distribution database (remote distributor) and the Subscriber on the same server (server B) in a transaction replication topology?

Please advise or point me to the appropriate documentation - I haven't found anything that addresses this specific question...thanks in advance,

Smitha

This is actually a rather popular configuration (at least the ones that I have seen) inside Microsoft as this allows you to upgrade the Distributor and Subscriber to a new release together and then use the Subscriber as a test bed for the new release while receiving continuous updates from the production system. You do need good network connectivity between the publisher and the distributor\subscriber as pulling snapshot data (or backup\restore for that matter) can easily saturate a 100Mbit Ethernet connection. So to minimize impact on the production system, you may want to get a dedicated connection between the two.

-Raymond

|||

Thanks Raymond. I shouldn't have to worry about the snapshot/backup+restore because I've used this second server as the backup files' location in the past (and still perform copy-only FULL production DB backups to this server). The restore takes a while since it reads from and writes to the same drive but this is OK for an operation that may happen 2-3 times a year.

Thanks again,

Smitha

Remote Database updates take long time to complete

Hi
I have have two linked SQL Servers and I am trying to get remote writes
working correctly (fast).

I have configured the DB link on both machines to:
Point at each others DB.
I have security set up to map each others server logins
and Server Options: Collation Compatible, Data Access, RPC, RPC Out, Use
Remote Collation all checked

My problem is that when a SP performs

Begin Transaction

Update Local Table

Update Remote Table

Commit Tran

It takes several seconds to complete. (about 7 seconds not acceptable to
us)
This is due to the remote update - how can I improve the response time?

example of a stored procedures that takes time

where ACSMSM is a remote (linked) SQL Server.

procedure [psm].ams_Update_VFE
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strVFEID varchar(16),
@.strDescription varchar(64),
@.strVFEVirtualRoot varchar(255),
@.strVFEPhysicalRoot varchar(255),
@.strAuditPath varchar(255),
@.strDefaultBranding varchar(16),
@.strIPAddress varchar(23)
as
declare @.strStep varchar(32)
declare @.trancount int

Set XACT_ABORT ON
set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
BEGIN TRANSACTION mytran
else
save tran mytran

/* start insert sp code here */

set @.strStep = 'Write VFE to MSM'

update
ACSMSM.msmprim.msm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID;

set @.strStep = 'Write VFE to PSM'

update
ACSPSM.psmprim.psm.VFECONFIG
set
DESCRIPTION = @.strDescription,
VFEVIRTUALROOT = @.strVFEVirtualRoot,
VFEPHYSICALROOT = @.strVFEPhysicalRoot,
AUDITPATH = @.strAuditPath,
DEFAULTBRANDING = @.strDefaultBranding,
IPADDRESS = @.strIPAddress
where
VFEID = @.strVFEID

/* end insert sp code here */

if (@.@.error <> 0)
begin
rollback tran mytran
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' + @.@.Error
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran

return 0Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> It takes several seconds to complete. (about 7 seconds not acceptable to
> us)
> This is due to the remote update - how can I improve the response time?

Have you tried moving the updates of the remote table to a stored
procedures that runs on the remote server, and then call that stored
procedure remotely? I would expect that to reduce the ping-pong over
the network.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have found the cause of the problem:

'For linked server DELETEs or UPDATEs, SQL Server retrieves data from the
table, performs any filtering that is necessary, and then performs the
deletes or updates through the OLEDB rowset. This processing can result in a
round-trip to the remote server for each row that is to be deleted or
updated. '

The solution is:

EXEC remserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

EXEC localserver.master.dbo.sp_executesql
@.stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94004CF4CD0FYazorman@.127.0.0.1...
> Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> > It takes several seconds to complete. (about 7 seconds not acceptable
to
> > us)
> > This is due to the remote update - how can I improve the response time?
> Have you tried moving the updates of the remote table to a stored
> procedures that runs on the remote server, and then call that stored
> procedure remotely? I would expect that to reduce the ping-pong over
> the network.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Remote Connections Enabled, but I still get error that Remote is not configured - Sql 2005

Fellow Devs,

I have an instance of SQL Server Express 2005 running on another box and I have Remote Connections enabled over both TCP/IP and Named Pipes, but on my other box I keep getting the error that the server does not accept Remote Connections.

Any ideas why this might be happening? Is there some other configuration?

start server configuration manager

click on protocols > TCP/IP and properties

go to IP addresses and under IPALL remove everythink from TCP dynamic port , under TCP Port enter port you want to use like 1433

save all changes restart service and should work

|||and if you have windows firewall on you have to allow to accept connection on this port|||

I don't see that option in my server configuration manager. I just see "File Server", "Application Server" and "Remote Access/VPN Server". Where do I modify thse settings?

Thanks!

|||

1) open SQL Server configuration manager
2) on the left under "Sql server 2005 network configuration" click on protocols for SQL
3) on right side right click TCP/IP > properties and tab IP addresses

|||

I was having the same problem and I followed your instructions, but now VS doesn't even detect the sql server. Any solutions?

Thanks.

|||

Swackhammer1:

I was having the same problem and I followed your instructions, but now VS doesn't even detect the sql server. Any solutions?

Thanks.

If VS is not detecting the SQL Server it may mean your SQL Server service maybe off. You may want to download the advanced version from the link below if not first get the eval and then spend $33 to get the developer edition. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

|||

looool what kind of solution is that ? doesn't work pay $33.

here is an article explaining step by step what you can do to enable remote connections - pretty much same idea i gave you , but when you will follow it must work

|||You have given the original poster very low level usually not adviced connection to SQL Server and it is not working, Express is best used for small company intranet hosted application nothing more. I have used SQL Server since 1998 and I have not got the need to connect to SQL Server on those layers. So mine will cost after 180 days but it is pain free.|||

i saw many of your posts and i KNOW that you work with SQL for long time and you know about it much more than i do.
I agree that SQL express is perfect for small project, intranet & for development and i believe that is what this person needs - when someone ask how to enable remote connections in SQL express- my guess would be that he/she doesn't work for BIG corporation that has billions of transaction / day

My point is that there is no reason to buy anything cos EXPRESS edition can work perfectly all you need is to spend few minutes with it and make the setup + maybe change firewall settings.

|||Yes but connecting to SQL Server through TCP port is not good advice because there is also the known UDP port and two others Microsoft admit to have reserved but only give to customers as needed which opens you up to known security issues. Microsoft was like Oracle selling the developer edition for hundreds we asked for the lower price for access and got it. Fighting with Express eats into development time.|||Ok. Here's the thing. I'm using SQL EXPRESS and VS 2005 on my machine, but the company I'm developing for has the full SQL SERVER 2005. Before I changed the tcp/ip setting, I could see my server in VS. After changing the settings though, it no longer shows up. I do have the service running. I double checked.|||OK. I decided to get the trial version of SQL SERVER 2005 for now. Let's see how that works out.|||

The links below from Microsoft covers most of what you need and some of what I have been trying to explain in details. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

|||

Caddre - very good links :)

BTWSwackhammer1 where in VS you can not see your SQL server ? in databse explorer when you click "add connection" ? if that is the problem just enter ".\SQLEXPRESS" in server name field or IP address of you server or "YOUR_COMPUTER_NEME\SQLEXPRESS"