Announcing A GUI Tool For Configuring Dialog Security And Routing

Apr 8, 2006

www.codeplex.com/slm

Setting up Service Broker routes, endpoints and security is just too hard. One has to run pages and pages of Transact-SQL code just to get the €˜Hello, world€™ example work between two separate SQL Instances and the chances of making a mistake are overwhelming.
Well, not anymore! I€™ve just uploaded into the Service Broker team code gallery a new GUI tool for doing just that: easily configure two services to be able to have conversations. The tool uses the €˜Service Listing€™ concept. A Service Listing is like an identity card for a service. It is an XML document that contains all the necessary information needed to establish a conversation with that service. When two parties need to establish a conversation, they can exchange the Service Listings of the two services and the tool will create the entire infrastructure needed to establish the conversation. Optionally it can also create the message types and contracts supported by a target service in the initiator service database....

View 3 Replies


ADVERTISEMENT

Certificate Security And Dynamic Routing

Aug 28, 2007

I've been reseaching on SSB and have read quite a number of posts on this forum that closely relate to what im trying to achieve. I have a solution im designing that ideally consists of a central server (SQLENTERPRISE) that will receive messages asynchronously from remote clients (SQLEXPRESS) spanning a wide geographical region over a GPRS virtual private network on a TCP/IP transport. This ideally is a star and spoke architecture and requirements dictate high level security, no loss of messages whatsover as well as high reliability and scalability.

To meet the security requirement in the context of the above scenario, i was thinking implementing both dialog and endpoint security using certificates would be ideal. I've downloaded some samples and have encountered problems simulating the above scenario on 3 machines (I Server & 2 Clients). Client1 sends messags successfully, the 2nd client doesnt possibly because the certificate on the server matches that to client1 since i ran that script first. How do i make it that the Server shares one certificate with all remote clients? Or is there a better way to configure SSB to work in this scenario?

Secondly i have hard coded the Server's IP Address in the Routes created on the Clients. Considering this is over a GPRS Virtual network, how can i make the clients dyamically "discover" the Server? The idea here is to make adding and setting up of new remote clients easy so that you can just plug them in to the existing network

Any help or pointers would be greatly appreciated.

PapaLee

View 1 Replies View Related

Transport Security Vs Dialog Security

Aug 3, 2006

In an environment where there are many initaitors speaking to a central target with frowarders in between, from what i can understand this best policy is to disable encryption on the endpoints, since dialog encryption will be enforced this is all that is really required, is this correct.

If the endpoints used encryption the message would need to be encrypted and decrypted at each forwarder resulting in slower perfromance, where as dialog encryption would only encrypt at the sender and decrypt at the target, so is this the best way to go?

Secondly is it best practice to open a dialog initally and send messages over this dialog for years never ending the conversation? This way the services only have to authenticate eachother once, if there are no reboots etc that is of course.

I would think performance wise sending each message and ending the conversation each time is a much greater overhead ? So would it be best practice to keep dialogs open and keep sending messages ?

Initally when i was learning service broker i thought that one must send a message and end the dialog until the next message, but i think the other way is the best option ?

Is this correct ?

Thanx

View 1 Replies View Related

Dialog Security Questions

Aug 10, 2006

Hi Remus

Thank you for the email reply.

However i am still not 100% clear, dialog security with certificates when there are hundreds of remote services seems a bit complicated.

I have a few more questions.

1. you said "When sending the first message (any direction), we look up the 'best' certificate for each user"

My inititor sevice is owned by dbo, i have created certificates only for named users not dbo, how would this work?

2. I am going to try settin gup this again, but at the central service i will create a seprate user for each remote service, authorize the remote certificate for this user and use this user in the remote service binding for that remote service, is this correct or am i still doing somehting wrong?

3. How does all of this relate to the endpoint security, i ahve the same scenario, 1 pricipal id with all remote certificates authorized for it for the endpoint? I have no idea how i would have to setup different user for each remote servcie as far as the endpoint goes as there is no remote service binding to dictate which user to use ?

I will keep try and experimenting maybe i will get it right sooner or later.

Thank You

View 6 Replies View Related

Dialog Security With Different Instances Of A Service

Sep 20, 2005

I am in the process of evaluating the use of Service broker for messaging in a load balanced configuration, and am not having any success.  My configuration is as follows.   1) Master database on Box #1 which is the initiator of all dialogs 2) Target database on Box #2 3) Target database on Box #3   One of my goals is that the databases on Box #2 and #3 are as close to identical as possible.   My routing table on Box #1 looks something like the following   Name remote_service_name broker_instance address TargetOne TargetService
E96DC67E-F696-4D93-8545-C2E92157E32D tcp://server1:4022/ TargetTwo TargetService
56607120-26AA-4AAA-B9E4-F14689C40E41 tcp://server2:4022/   My messaging process begins with a dialog initiated from the master database (Box #1) to each of the target services.  At this time, only the first server "tcp://server1:4022/" is receiving messages.  The sys.transmission_queue shows a message outbound to "tcp://server2:4022/" with a blank status.   I believe that my delivery problem has something to do with the resolution of the dialog certificate need for the conversation.  The MSDN documentation states that:   "Service Broker uses a remote service binding to locate the certificate to use for a new conversation...The certificate for user_name must correspond to the certificate for a user in the database that hosts the remote service."    I am confused as to how I would configure remote service binding if one can not create more than one binding for the same remote_service_name.   The following throws an exception on the last creation of the binding.
---CODE SNIP---
CREATE CERTIFICATE UFEDlgCertTargetPublic

View 3 Replies View Related

Confusion About Dialog Security Steps.

Aug 3, 2006

Hi There

I have done the following.

2 Servers across the net work Server A and Server B.

I have created private keys at each and copied to public keys across.

I create an endpoint at each with validation by the certificates. I then create appropriate users and logins at each and import the public certificates with authorization to the users who have send permission on the endpoints.

That is transport security sorted and it works.

Now from what i have learnt from examples, to setup dialog security i do the following.

create a private key in each DB :

create certificate Store001DialogPri

with subject = 'Store001DialogPri',

start_date = '07/20/2006'

active for begin_dialog = on;

go

I then copy the public key to each server, create a user only in each DB and import the public keys with authorization on the user. And grant send to the appropriate servcies to the user.

I then create appropriate remote service bindings with this user.

Now this works for me. Everythign seems A OK.

However i am going thorugh the Service Broker "bible", and there are a ton of steps i am not doing but my setup works ???

Steps i am not doing for example is when i create the private keys in the DB i should authorize them to a user i create who is then gratnted CONTROL permission on the SERVICES.

Now i do not do this but everything seems to be working. I thought i finally understood dialogs security but now i am totally confused ?

Is what i am doing correct ? If so why are these all these additinal steps mentioned ? WHat am i missing ?

Thanx

View 3 Replies View Related

Dialog Security And Message Encryption

Apr 25, 2006

I understand that Dialog Security + certificates can be used to encryption individual dialogs. I have several demos working now that do just this.However, I don't fully understand exactly when the messages are encrypted, and if they are ever written to a queue on the initiating service prior to being encrypted. I want to make sure that admins can't simply query the transmission queues to get clear text messages, because I have strict requirements that I encrypt all personal data that is stored anywhere in a database.
BOL is a little unclear on this topic. The relevant doc reads:For a dialog that uses security, Service Broker encrypts all
messages sent outside a SQL Server instance. Messages that remain within a SQL
Server instance are never encrypted. In dialog security, only the database that
hosts the initiating service and the database that hosts the target service need
to have access to the certificates used for security. That is, an instance that
performs message forwarding is not required to have the capability to decrypt
the messages that the instance forwards.Does this imply that message destined for an external service aren't encrypted until they leave the instance? Or does Service Broker figure out that the message is destined for a remote service and therefore applies encryption to the message_body prior to the message hitting the transmission queue on the initiating service?Many thanks, Kevin

View 6 Replies View Related

Service Broker Dialog And Transport Security

May 25, 2006

what is the difference between the Dialog security and the transport security?

If I disable the dialog security can the messages be sent to the a different SQL server instance. As the transport security will encrypt the messages.

If I don't create a certificate to be used by the transport security can the messages be sent to another SQL server instance?

Can a message be sent to another SQL Server instance without creating a REMOTE Service Binding?

View 1 Replies View Related

In Service Broker Message Dialog Security Is Not Available For This Conversation...

Jul 23, 2007

when ever I send my message thru Service Broker I am getting an error message like this "



"Dialog security is not available for this conversation because there is no remote service binding for the target service. Create a remote service binding, or specify ENCRYPTION = OFF in the BEGIN DIALOG statement."



This I found in sys.transmission_queue

Please reply with your comments

View 1 Replies View Related

Cross DB Dialog Security Issues. Was: Can't Route To Another LOCAL Broker Instance

May 31, 2007

Hi Remus,



I am experiencing the same problem, and I can't get the easy fix to work. I drop and create the DB's in between tests, so it is not related to having an old certificate in the DB, as in the case of Tilfried.



The situation is as follows:

DB1 owned by login1, has a user for login2; this DB is for the initiator

DB2 owned by login2, has a user for login1; this DB hosts the target

Both DB's have TRUSTWORTHY flag set to ON

Error in sys.transmission_queue: 'Error 916, State 3: The server principal "Login1" is not able to access the database "DB2" under the current security context.



Going on a limp, I decide to add a remote service binding in DB1, binding the user for Login2 to the target service, even though BOL explicitly states that this is only required for cross-server communications. This does change the situation - I still get an error, but a new message is sys.transmission_queue: "Dialog security is unavailable for this covnersation because there is no certificate bound to the database principal (Id: 5). Either create a certificate for the principal, or specify ENCRYPTION = OFF when beginning the conversation." I already know that the first option works, but I wanted to get the simple solution running. As for the second option, I doublechecked and the initiating procedure DOES already specify ENCRYPTION = OFF in the BEGIN DIALOG CONVERSATION command. My theory is that the remote service binding somehow forces SB to use encryption, but (a) that is not stated in the error message, and (b) if so, then how to get the messages sent over to the target service without using the binding?



==> EDIT: Just saw that you confirmed this theory in your last reply to Tlifried. So I am indeed back to having to find out how to get this to work without remote service binding - it should be possible, but how???



BTW, SELECT @@VERSION shows that I'm on build 3054, in case it matters.



Between all the errors in BOL and less than helpfull error messages produced by SB, I feel like I'm slowly losing my sanity. Please help!



Best regards,



Hugo Kornelis

View 6 Replies View Related

Can Not Access Security Page On Web Administration Tool

Dec 1, 2006

I get the following error message when i click the Security tab on my websites' Administration tool. All the other tabs are working properly.
There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 1802 and the SqlException message is: CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Cannot create file 'C:SAFETYDATABASEAPP_DATAASPNETDB_TMP.MDF' because it already exists. Change the file path or the file name, and retry the operation. Creating the ASPNETDB_652db56c1d7d4c9f94da67107d9c917a database...
Any help is appreciated.

View 1 Replies View Related

Announcing SQL Server Everywhere Edition

Apr 6, 2006

Today we are announcing SQL Server Everywhere Edition, a light weight database designed for client applications that run from desktops to mobile devices. Pl see the section 'Dynamic Applications' in the Microsoft's Data Platform Vision and Roadmap from Paul Flessner, Sr VP, Microsoft.

Microsoft€™s Data Platform Vision and Roadmap: Your Data, Any Place, Any TimeMicrosoft€™s Data Platform Vision and Roadmap: Your Data, Any Place, Any Time

http://www.microsoft.com/sql/letter.mspx

The new product is based on the familiar SQL Server Mobile Edition.

-Durga Gudipati

Program Manager, SQL Server Everywhere Edition

View 5 Replies View Related

Announcing - SQL Server Database Publishing Wizard CTP 1

Oct 17, 2006

The SQL Server group at Microsoft has just launched the SQL Server Hosting Toolkit with the release of the Database Publishing Wizard Community Technology Preview 1.The objective of the SQL Server Hosting Toolkit is to enable a great experience around hosted SQL Server.  The Database Publishing Wizard works toward this mission by making it easy to upload a database from a development box up to a shared hoster.  In its first incarnation, the Database Publishing Wizard is a command line tool that generates a T-SQL script designed to be executed in the script execution windows provided by most hosters in their database management consoles.  In the coming months we'll be adding a GUI and building seamless integration between the tool and an upload service we'll provide to hosters for deployment.Details on the Database Publishing Wizard as well as the download can be found at http://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=Database%20Publishing%20Wizard.We plan to be releasing advances in the Toolkit frequently so check out this first CTP and give us your feedback!Thanks,Dan WinnSenior Program Manager, Microsoft SQL Server

View 15 Replies View Related

Routing SQL Traffic And Replication

Oct 15, 1999

Can anyone HELP!!
I have a system set up with 2 servers, the master server replicating the data to the backup server. Each server has two network cards, 1 which links directly to the other server.
The problem is the replication is being routed round the network making the direct link useless.
How can this be ammended!!
They are both logging into the same primary controler.

I am open to sugestions

View 1 Replies View Related

Routing File To Printer

Mar 1, 2007

Is there a way to route a file directly to a printer? I have a process that creates PDF file in a known location and I would like to route them directly to the printer after creation.

View 2 Replies View Related

Routing Fro A Named Instance?

Mar 12, 2008



Hi There

I cannot find anything online or in BOL about the proper format for a named instance in the routing address.

is it simply

ADDRESS = 'TCP://SERVERNAMEINSTANCENAME:4026'

or do you need to provide the port number for the instance.Not sure if TCP recognises the instance part?

Thanx

View 3 Replies View Related

Service Broker And Routing

Jul 10, 2006

Hi

We are currently starting new project and evaluating possibility of using Service Broker in it but seems there could be some problems
We have a central server with a central database and several hundred of €œmobile€? users. Information ( actually a subset which is specific to a particular user ) should be sent from/to central database and we wanted to use Service Broker for this.
When you set up a route in Service Broker you need to define Network address which I believe should be an IP address or DNS name.
The problem for us is that our €œmobile€? users do not have static IP addresses and I am not sure we can assign a unique dns to all of them.
Mobile users can connect to internet in different places€Ś and hence get different IP addreses.
Is there a solution to this problem?
Rsgards,
Leonid.

View 2 Replies View Related

SQL 2012 :: Read-only Routing In AG Group

Mar 16, 2015

Unable to access the 'T' database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation. Changed database context to 'T'.

I have 2 sql servers running in an AG group. SQL 2 is fully syncd and is used for read only reporting.The connection from the listener using ReadOnly works and connects to SQL 2. The problem is, when a database in SQL 2 is suspended from the AG group, the listener does not connect to the SQL 1 (primary).

The AG group is setup exactly like this:

Readable Secondary = Yes for both SQL 1 and 2, Availability Mode = Synchronous

why the listener does not connect to primary server?Connection in primary was set to "Allow read/write connections" for the AG. This meant "Connections where the Application Intent connection property is set to ReadOnly are not allowed. " Setting to "Allow All Connections" worked.

View 0 Replies View Related

Classification Has Been Delayed Because The Routing Information...

Apr 25, 2007

Any ideas on why I get this message in the transmission status:



Classification has been delayed because the routing information is currently being updated.



I did create/drop some routes because I had pointed them to the wrong service, but that was some time ago.



I also just tried to restart the sql instance and it hung on stopping so I'm assuming something with the routing caused the issue.



Any ideas?



Thanks.

View 3 Replies View Related

SQL 2012 :: AlwaysOn Read-intent Via SSMS Not Routing?

Nov 15, 2014

I setup an AG with a listener (AO1) on three servers.

Server1 = Primary, Readable Secondary = No
Server2 = Secondary, Readable Secondary = Read-intent only
Server3 = Secondary, Readable Secondary = Read-intent only

Connecting to AO1 with default settings, I end up on Server1.

If I attempt to connect to AO1 with specifying ;ApplicationIntent=ReadOnly (with or without specifying the db name) in SSMS, still lands me on Server1.

Also on topic, in this scenario I would actually want Server1 to be a readable secondary (in the event of a fail-over). So If I did set it as Yes, even If I do get the issue above working, won't I always land on Server1 then?

View 9 Replies View Related

Recovery :: Read-only Routing With Single Application Connection?

Nov 30, 2015

My application supports a single database connection and in the app console I can produce reports. If I include the app database in an AlwaysOn availability group with a read-intent replica will SQL automatically route the “selects” to that second instance thus offloading my application’s reporting activities or I need a separate db connection (maybe from a reporting app or cli) with a connection specifying read-only intent?

View 4 Replies View Related

SQL Server Admin 2014 :: AlwaysOn Read-Only Replica Routing

Jun 22, 2015

Having an annoying AG/AO problem with the read only routing side of it.

Let me give some specifics first:

2 SQL Server Instances, Not Clustered.
Availability Group is named 'Ireland'

There is a primary Replica and a Secondary Replica, named:

'IrelandPrimary' and 'IrelandSecondary'

There is a listener configured with the name 'ListenIreland' on Port 14330 (the two 3's are correct)

Read Only Routing URLS are configured as follows:
IrelandPrimary tcp://Ireland.dom.local:49891ALL
IrelandSecondary tcp://Ireland.dom.local:49841ALL

So now my problem:

When I try to connect using the ApplicationIntent=Readonly; or even using -K ReadONLY in sqlcmd I get the error telling me that my connection was actively refused.

This is connecting to the Listener, not the instance itself - that works fine. I'm at a bit of a loss now.

To explain what I am trying to achieve is a for a connection to be redirected to the secondary replica when its set for read-intent.

I've just noticed that it only fails when I specify ApplicationIntent=ReadOnly; If I omit the Intent It connects to the read-write database instead.

View 3 Replies View Related

Asp.net Confgiguration Tool. Tool Keeps Timing Out

Mar 2, 2006

is it possible to change the settings on this tool. i want to have it so that it does not time out at all? or is that possible??

View 1 Replies View Related

No Help Dialog In Vs Ssis

Mar 10, 2006

At this point in time, I can think of nothing more annoying than a button that doesn't work. Especially when that button is the HELP button.

Inside SSIS designer, say I add a "derived column tool" to the data flow task. Now if I double-click on the "derived column tool", and then click on the help button in the dialog box that appears, NO help is displayed. What gives?

I am using 64 bit editions of win2k3 and ss05.

View 5 Replies View Related

Looking For A Tool/ Middleware Tool Which...

Jul 23, 2005

Hi all.I'm looking for a tool which should act like some kind of middleware/logical layer bewtween the SQL server and the webbased user interface.- It should be possible to easily create simple web forms (only datainput and output) without programming effort by "clicking" the fieldsand their order on the web mask within an admnistrative interface.- It should also be bossible to add "new fields" to the database,including field type, permissions, value lists etc. (excluding anybusiness logic) without programming effort by administration.I know, that most workflow tools or "Trouble Ticket Tools", based ondatabases have this feature to easily configure new masks and add newfields to the database, but i need it as an tool-unspecific layer forthe MS SQL Server.Thank you very much for your feedback, any ideas are welcome!Best regards, Heiko.

View 1 Replies View Related

Cannot Show Requested Dialog

Dec 17, 2007

Hi,when i try to see the properties of database "sales" (sql server express 2005) in  Management Studio Express, i get this error message: (besides, when i try to expand the database, i get the error that it's emty)Cannot show requested dialog.ADDITIONAL INFORMATION:Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)------------------------------An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)------------------------------The server principal "Myserveradmin" is not able to access the database "sales" under the current security context. (Microsoft SQL Server, Error: 916)But the webapplication still runs: i can insert records, update, delete ... from asp.net.Any way to recover it?ThanksTartuffe

View 4 Replies View Related

Blank Dialog Box When Starting SQL

May 18, 2006

When I start SQL Server Management Studio (Sql 2005), a blank dialog box pops up with nothing in it.  The title in the dialog box is "Microsoft SQL Server Management Studio" and it has a yellow triangle with an explanation point in it but there is no message just an OK button.  I have to click the OK button to continue on to connect to the Sql databases.  It does this everytime I open it.  Anyone else getting this and how can I get rid of it? 

View 2 Replies View Related

Connect To SQL Server Dialog

Jul 23, 2005

Hi,Is it possible to avoid the last used server being in Query Analyzer'Connect to SQL Server' dialog by default(when I click on File/Connectin Query Analyzer) - I don't want to apply 'truncate table' scripts toa production server by mistake!Thanks

View 1 Replies View Related

How To Identify A Dialog As Encrypted?

Oct 3, 2006

I am trying to lookup a dialog from conversation_endpoints, however if a dialog was created with the encryption setting to ON and thereis no master Key in the database then the record put in the conversation_endpoints is the same as one without encryption.

How can I distinguish between the one requested with no ecryption and requested with encryption but setup with none due to the lack of a key?

View 2 Replies View Related

The Dialog Has Exceeded The Specified LIFETIME

Mar 30, 2006

Hi,

I'm trying to get the HelloWorld_CLR project that comes with the SQL 2005 samples download to work, which it seems to. However, after I run the application a few times, an exception indicating "the service queue ClientQueue is disabled." After running the following query I notice the body of the error message is consistently "The dialog has exceeded the specified LIFETIME."

select service_name as [SERVICE],

message_type_name as [TYPE],

CAST(message_body as nvarchar(max)) as BODY

from ClientQueue

I believe that the problem is a poison message causing a RECEIVE statement to get rolled back 5 times, disabling the queue. My question is: How do I troublshoot the issue from this point?

Thanks,

Chris

View 1 Replies View Related

Renaming Package Dialog

May 30, 2006

I created a new SSIS solution, right-clicked the default name "Package.dtsx" (under SSIS Packages), then renamed it. A dialog appears asking "Do you want to rename the package object as well"?

What are they referring to and what are the consequences of doing/not doing so?



Barkingdog

View 1 Replies View Related

Routing To Diff Instance Of SQL Server On Same Server

Aug 4, 2006

Remus,

thank you for your reply on (microsoft.public.sqlserver.notificationsvcs).

one more question :

Server_2 == Server_3

so I set up a route for db2:

CREATE ROUTE [rem_s2_route] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'rem_s2' , ADDRESS = N'TCP://192.168.0.2:4022'

to create route for db3 (sql express) I tried

CREATE ROUTE [rem_s3_route] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'rem_s3' , ADDRESS = N'TCP://192.168.0.2sqlexpress:4022'

this failed, what am I doing wrong or did I lost the plot? How do I diff beteen more than one instance of sql server on the same server? And can the port be the same on each instance of SQL Server?

thank you in advance

View 1 Replies View Related

Open Query File Dialog Box

Oct 7, 2007

Hi,
SQL2K+SP4
When I load up Query Analyzer and click File -> Open to open up a query
file, the open file dialog is always sorted by - i don't know
alphabetically.
How can I set the default to open up with files ordered alphabetically?

Thanks
Helena

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved