Does not have permission to register endpoint

I came across with the problem that I cannot create an endpoint on Windows Server 2003 (SP1). It's said:

Msg 7850, Level 16, State 1, Line 2

The user 'yyyyy\xxxx' does not have permission to register endpoint 'zzzzzz' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.

Msg 7807, Level 16, State 1, Line 2

An error ('0x80070005') occurred while attempting to register the endpoint 'SecurityServices'.

My code looks loke this:

IF EXISTS (

SELECT name from sys.http_endpoints

WHERE name = 'zzzzzz'

)

DROP ENDPOINT zzzzzz

GO

CREATE ENDPOINT zzzzzz

STATE = STARTED

AS HTTP (

path='/sql/zzzzzz',

AUTHENTICATION=(INTEGRATED),

PORTS = (CLEAR)

)

FOR SOAP(

WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'),

WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),

WSDL = DEFAULT,

BATCHES=ENABLED)

GO

-- End of Script --
The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005.

If anyone has an idea about my problem, please help me !!!

Thank you,
POP

I believe the problem you run into is because when you create an endpoint, you are not executing under the logged in user, but as the account the SQL Server runs under. In your case the account on the remote server most likely have very restricted priviliges. To fix this you need to reserve the namespace you are going to create, before you can actually create it.
To reserve it, you run the un-doc:ed stored procedure sp_reserve_http_namespace. You run it with the macine name (as it will appear in the site param in CREATE ENDPOINT), portnumber and virtual directory name as in the path param in CREATE ENDPOINT.
An example would be:
sp_reserve_http_namespace N'http://Perth:80/emp', which then would have the following CREATE ENPOINT statement:
<<<<<<<<<<<<<<<
create endpoint EmpClass
state = started
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
>>>>>>>>>>>>>>>
Hope this helps!!
Niels

|||Thank you so much Niels (again),

It's works !!!!

At first, I try to use >> sp_reserve_http_namespace N'http://Perth:80/emp'
first but it said >> A reservation for this HTTP namespace (http://Perth:80/emp) already exists

But when I look at your code
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
I don't have the value of the site (yellow highlighted part). Then I add that part and when I run the code on the server, it works !!!!.

Without that line, I can create an end point on my local computer but not on the server (Cluster Servers). So from now on, I'll have that line all the time.

Thank you again Niels,

POP|||It works. Thank you.

Does not have permission to register endpoint

I came across with the problem that I cannot create an endpoint on Windows Server 2003 (SP1). It's said:

Msg 7850, Level 16, State 1, Line 2

The user 'yyyyy\xxxx' does not have permission to register endpoint 'zzzzzz' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.

Msg 7807, Level 16, State 1, Line 2

An error ('0x80070005') occurred while attempting to register the endpoint 'SecurityServices'.

My code looks loke this:

IF EXISTS (

SELECT name from sys.http_endpoints

WHERE name = 'zzzzzz'

)

DROP ENDPOINT zzzzzz

GO

CREATE ENDPOINT zzzzzz

STATE = STARTED

AS HTTP (

path='/sql/zzzzzz',

AUTHENTICATION=(INTEGRATED),

PORTS = (CLEAR)

)

FOR SOAP(

WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'),

WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),

WSDL = DEFAULT,

BATCHES=ENABLED)

GO

-- End of Script --
The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005.

If anyone has an idea about my problem, please help me !!!

Thank you,
POP

I believe the problem you run into is because when you create an endpoint, you are not executing under the logged in user, but as the account the SQL Server runs under. In your case the account on the remote server most likely have very restricted priviliges. To fix this you need to reserve the namespace you are going to create, before you can actually create it.
To reserve it, you run the un-doc:ed stored procedure sp_reserve_http_namespace. You run it with the macine name (as it will appear in the site param in CREATE ENDPOINT), portnumber and virtual directory name as in the path param in CREATE ENDPOINT.
An example would be:
sp_reserve_http_namespace N'http://Perth:80/emp', which then would have the following CREATE ENPOINT statement:
<<<<<<<<<<<<<<<
create endpoint EmpClass
state = started
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
>>>>>>>>>>>>>>>
Hope this helps!!
Niels
|||Thank you so much Niels (again),

It's works !!!!

At first, I try to use >> sp_reserve_http_namespace N'http://Perth:80/emp'
first but it said >> A reservation for this HTTP namespace (http://Perth:80/emp) already exists

But when I look at your code
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
I don't have the value of the site (yellow highlighted part). Then I add that part and when I run the code on the server, it works !!!!.

Without that line, I can create an end point on my local computer but not on the server (Cluster Servers). So from now on, I'll have that line all the time.

Thank you again Niels,

POP|||It works. Thank you.

does anyone know about this err msg?

sql2k, nt5.
I have a stored procedure drop and re-create webtask... this is the msg i've
got when running it.
Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
[SQLSTATE 42000]
does anyone know what it's about? thank you.
Steve,
You may find an explanation of this at:
http://support.microsoft.com/default...b;en-us;834116
Hope it helps,
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> sql2k, nt5.
> I have a stored procedure drop and re-create webtask... this is the msg
i've
> got when running it.
> Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
> [SQLSTATE 42000]
> does anyone know what it's about? thank you.
>
|||the job owner is sa.
I have read that article before and don't think it's related to my problem.
because the errors come and go and not related to a particular record in the
table either.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Steve,
> You may find an explanation of this at:
> http://support.microsoft.com/default...b;en-us;834116
> Hope it helps,
> Russell Fields
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> i've
statement.
>
|||Steve,
Sorry that I don't know a lot more than this. However, it is interesting
that you are running as 'sa', which I would interpret to mean you are using
Method 2 to try to work around the problem. Method 1 is to use a domain
account that has admin permissions on your server. (Interestingly, both
workarounds are "not recommended" by Microsoft.)
Anyway, have you tried the Method 1 workaround? Did it change things at
all?
You mention that the errors come and go. Do you have any idea of whether
something else in your security landscape is changing?
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:utbyTRMKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> the job owner is sa.
> I have read that article before and don't think it's related to my
problem.
> because the errors come and go and not related to a particular record in
the[vbcol=seagreen]
> table either.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...
msg
> statement.
>

does anyone know about this err msg?

sql2k, nt5.
I have a stored procedure drop and re-create webtask... this is the msg i've
got when running it.
Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
[SQLSTATE 42000]
does anyone know what it's about? thank you.Steve,
You may find an explanation of this at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834116
Hope it helps,
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> sql2k, nt5.
> I have a stored procedure drop and re-create webtask... this is the msg
i've
> got when running it.
> Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
> [SQLSTATE 42000]
> does anyone know what it's about? thank you.
>|||the job owner is sa.
I have read that article before and don't think it's related to my problem.
because the errors come and go and not related to a particular record in the
table either.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...
> Steve,
> You may find an explanation of this at:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834116
> Hope it helps,
> Russell Fields
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> >
> > sql2k, nt5.
> > I have a stored procedure drop and re-create webtask... this is the msg
> i've
> > got when running it.
> >
> > Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL
statement.
> > [SQLSTATE 42000]
> >
> > does anyone know what it's about? thank you.
> >
> >
>|||Steve,
Sorry that I don't know a lot more than this. However, it is interesting
that you are running as 'sa', which I would interpret to mean you are using
Method 2 to try to work around the problem. Method 1 is to use a domain
account that has admin permissions on your server. (Interestingly, both
workarounds are "not recommended" by Microsoft.)
Anyway, have you tried the Method 1 workaround? Did it change things at
all?
You mention that the errors come and go. Do you have any idea of whether
something else in your security landscape is changing?
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:utbyTRMKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> the job owner is sa.
> I have read that article before and don't think it's related to my
problem.
> because the errors come and go and not related to a particular record in
the
> table either.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...
> > Steve,
> >
> > You may find an explanation of this at:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;834116
> >
> > Hope it helps,
> > Russell Fields
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> > >
> > > sql2k, nt5.
> > > I have a stored procedure drop and re-create webtask... this is the
msg
> > i've
> > > got when running it.
> > >
> > > Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL
> statement.
> > > [SQLSTATE 42000]
> > >
> > > does anyone know what it's about? thank you.
> > >
> > >
> >
> >
>

does anyone know about this err msg?

sql2k, nt5.
I have a stored procedure drop and re-create webtask... this is the msg i've
got when running it.
Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
[SQLSTATE 42000]
does anyone know what it's about? thank you.Steve,
You may find an explanation of this at:
http://support.microsoft.com/defaul...kb;en-us;834116
Hope it helps,
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> sql2k, nt5.
> I have a stored procedure drop and re-create webtask... this is the msg
i've
> got when running it.
> Msg 16805, Sev 11: SQL Web Assistant: Could not execute the SQL statement.
> [SQLSTATE 42000]
> does anyone know what it's about? thank you.
>|||the job owner is sa.
I have read that article before and don't think it's related to my problem.
because the errors come and go and not related to a particular record in the
table either.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...
> Steve,
> You may find an explanation of this at:
> http://support.microsoft.com/defaul...kb;en-us;834116
> Hope it helps,
> Russell Fields
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:On50IjJKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> i've
statement.[vbcol=seagreen]
>|||Steve,
Sorry that I don't know a lot more than this. However, it is interesting
that you are running as 'sa', which I would interpret to mean you are using
Method 2 to try to work around the problem. Method 1 is to use a domain
account that has admin permissions on your server. (Interestingly, both
workarounds are "not recommended" by Microsoft.)
Anyway, have you tried the Method 1 workaround? Did it change things at
all?
You mention that the errors come and go. Do you have any idea of whether
something else in your security landscape is changing?
Russell Fields
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:utbyTRMKEHA.2580@.TK2MSFTNGP12.phx.gbl...
> the job owner is sa.
> I have read that article before and don't think it's related to my
problem.
> because the errors come and go and not related to a particular record in
the
> table either.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OUX#goKKEHA.2888@.TK2MSFTNGP09.phx.gbl...
msg[vbcol=seagreen]
> statement.
>

Do you understand this Error msg (ERROR Msg 512, Level 16, State 1, Line 33)

I have created a procedure which start by fecthing data from DB-X and put in into the temporary memory. what im trying to do now is to take data from temporary memory insert/update DB-Y.

But now I get this ERROR Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

HERE IS MY PROCEDURE

DECLARE @.MineID int,
@.MineName varchar(80),
@.MineDescription [varchar](80) ,
@.MineLocation varchar(80),
@.Country varchar(80),
@.Northing float,
@.Easting float,
@.Elevation float,
@.Latitude float ,
@.Longitude float,
@.MineLogo varbinary(max)

DECLARE MYCURSOR CURSOR
FOR SELECT * FROM [TLC].[DBO].[MINE]
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO
@.MineID,
@.MineName ,
@.MineDescription ,
@.MineLocation ,
@.Country ,
@.Northing ,
@.Easting ,
@.Elevation ,
@.Latitude,
@.Longitude,
@.MineLogo
WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.MineID =(select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
BEGIN UPDATE [TEST].[dbo].[MINE1]
SET MineName = @.MineName
,MineDescription = @.MineDescription
,MineLocation = @.MineLocation
,Country =@.Country
,Northing = @.Northing
,Easting = @.Easting
,Elevation = @.Elevation
,Latitude = @.Latitude
,Longitude = @.Longitude
,MineLogo =@.MineLogo
where MineId = @.MineID

END
IF @.MineID <> (select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
INSERT INTO [TEST].[dbo].[MINE1]
(MineID
,MineName
,MineDescription
,MineLocation
,Country
,Northing
,Easting
,Elevation
,Latitude
,Longitude
,MineLogo )
VALUES (
@.MineID
,@.MineName
,@.MineDescription
,@.MineLocation
,@.Country
,@.Northing
,@.Easting
,@.Elevation
,@.Latitude
,@.Longitude
,@.MineLogo)

FETCH NEXT FROM MYCURSOR INTO

@.MineID,
@.MineName ,
@.MineDescription ,
@.MineLocation ,
@.Country ,
@.Northing ,
@.Easting ,
@.Elevation ,
@.Latitude,
@.Longitude,
@.MineLogo

END
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
CLOSE MYCURSOR
DEALLOCATE MYCURSORYou don't need cursors for this purpose. Please look at following sample that demonstrates how you should synchronize your data:

create database temp
create database temp1
go
create table temp.dbo.mine(id int identity, name nvarchar(max))
create table temp1.dbo.mine(id int identity, name nvarchar(max))

insert temp.dbo.mine(name) values('UpdatedValue1')
insert temp.dbo.mine(name) values('UpdatedValue2')
insert temp.dbo.mine(name) values('UpdatedValue3')
insert temp.dbo.mine(name) values('UpdatedValue4')
insert temp.dbo.mine(name) values('UpdatedValue5')
insert temp.dbo.mine(name) values('NewValue6')
insert temp1.dbo.mine(name) values('OldValue1')
insert temp1.dbo.mine(name) values('OldValue2')
insert temp1.dbo.mine(name) values('OldValue3')
insert temp1.dbo.mine(name) values('OldValue4')
insert temp1.dbo.mine(name) values('OldValue5')
select * from temp.dbo.mine
select * from temp1.dbo.mine

update temp1.dbo.mine
set
name = Source.name
from temp.dbo.mine Source
where mine.id = Source.id

set identity_insert temp1.dbo.mine on
insert temp1.dbo.mine(id, name)
select id, name
from temp.dbo.mine
where id not in (select id from temp1.dbo.mine)
set identity_insert temp1.dbo.mine off

select * from temp.dbo.mine
select * from temp1.dbo.mine

drop database temp
drop database temp1|||Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test. And these two database they are not integrated and we can't intergrate them.So what will happen is: if the data exist it can update else insert a new record. I'm not creating database from scratch.

this error is trigged by these statemement

IF @.BlasterID <> (select BlasterID from Blasters )
INSERT INTO [TEST].[dbo].[Blasters]

BELOW IS THE ERROR
{Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.}

It seems like SQL doesn't want that , I have also tried the IF NOT EXIST clause but still it doesn't help. Please if you have any ideas agains on how to handle this with SQL please help|||

Quote:

Originally Posted by Sally1053

Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test

Have you looked at sample I provided? It demonstrates how to synchronize data from two different databases in more efficient way than you do.
--
Concerning your particular problem with
IF @.BlasterID <> (select BlasterID from Blasters ):
You can compare a variable (@.BlasterID) with resultset (select BlasterID from Blasters) only when resultset contains a single row with single column. In your case resultset contains all rows from Blasters table, not one. Correct solution is:
IF NOT EXISTS (select * from Blasters WHERE BlasterID = @.BlasterID)