Script out Endpoints

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Cantley - 12 Feb 2008 15:27 GMT

Is there a way to script out the endpoints. I get info back when I do a
select * from sys.endpoints but how do I figure out the endpoints
authorization. I see a principal_id but what system table does that relate
to.

thanks,
jc

Reply to this Message

Tibor Karaszi - 12 Feb 2008 20:53 GMT

You mean "script" like generating the TSQL CREATE command?`Management Studio do correctly script out
my SOAP endpoint, including the AUTHORIZATION clause (I'm on sp2).

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Is there a way to script out the endpoints. I get info back when I do a select * from
> sys.endpoints but how do I figure out the endpoints authorization. I see a principal_id but what
> system table does that relate to.
>
> thanks,
> jc

Reply to this Message

John Cantley - 12 Feb 2008 22:40 GMT

Tibor,

Yes exactly. That is what I need. What is the command to do that. I have
all the information except the autorization clause and I need that.

Thanks,
jc

> You mean "script" like generating the TSQL CREATE command?`Management
> Studio do correctly script out my SOAP endpoint, including the
[quoted text clipped - 7 lines]
>> thanks,
>> jc

Reply to this Message

Tibor Karaszi - 13 Feb 2008 19:31 GMT

There's no TSQL command to generate a CREATE script. The code to generate such DDL script lives
inside the SMO API.

However, if you want to know where in the meta-data you find who owns the endpoint, check out
sys.endpoints (the principal_id column) and sys.server_principals.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Tibor,
>
[quoted text clipped - 13 lines]
>>> thanks,
>>> jc

Reply to this Message

Craig Thomas - 14 Feb 2008 06:46 GMT

John,

If you use the Microsoft SQL Server 2005 Management Studio (no, not that SQL
Server 2005 Management Studio Express, the other one, the one that costs MSDN
dollars), you will find the Service Broker Objects in the "Object Explorer":

[-] MyServer (SQL Server 9.0.3054 - sa)
...[+] Databases
...[+] Security
...[-] Server Objects
.......[+] Backup Devices
.......[-] Endpoints
...........[+] System Endpoints
...........[+] Database Mirroring
...........[-] Service Broker
...............[ ] SapEndpoint <<<=== right-mouse click to script
...........[+] SOAP
...........[+] TSQL

Here's what I get:
/****** Object: Endpoint [SapEndpoint] Script Date: 02/13/2008 22:29:50
******/
CREATE ENDPOINT [SapEndpoint]
    AUTHORIZATION [sa]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
    FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = CERTIFICATE [SapTransportCertificate]
, ENCRYPTION = REQUIRED ALGORITHM RC4)

This includes the authorization and certificate information.

If you're in the business of finding the information from the system views,
it is all there. SQL Server Management Studio makes it much easier to access.

HTH...

Thanks,
--Craig.

Reply to this Message