BCN service questions

Hi

some questions about BCN service :

- WE have several remote clients and we don’t know their addresses.

- We create a BCN Service for dynamic routes configuration.

- Each time we send a message to a specific user message is sent to BCN asking for a route

- Messages for a specific user can have specific conversation group id

( Question : if we send several message to the client with no route does it mean that there will be several messages to BCN ?.

My guess that “missing route” message is sent to BCN for every new conversation but not for several messages inside one conversation. )

- BCN service doesn’t answer immediately because it doesn’t know the address.

- There will be an event in the system ( client is connected and provide an address ) when we know his address and only then BCN will answer to the message asking for the route for this client.

( Question : if we have several messages ( probably several conversations ) asking route for a specific client does it mean we need to answer to all these messages/conversations? ).

Will this work?

Leonid.

When the service broker service begins a dialog with the BCN service and sends a missing route message, it will not send any more messages until the BCN service ends the dialog. When the dialog is ended, broker will try to classify dialogs targetting the missing service again. If no routes are found, the messages remain in DELAYED state for 10 minutes. Then service broker service will begin a new conversation with the BCN service to re-request the missing route.

|||

So if I have several clients with no routes information then BCN is not a solution ...

I mean the scenario I have presented in my question won;t work?

|||

What do you mean by several client? If you mean target services, Service Broker will send a request to BCN for each service with a missing route. So I don't see why this shouldn't work.

Basic SQL Connection

Hi all, having trouble with my first sql communication. I've got hosted service with an SQL database i've populated with a row.

When it gets to the third line the page crashes with an error.

SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;");// +"Integrated Security=True");

SqlCommand cmd = new SqlCommand("SELECT UserName FROM Users",connection);

SqlDataReader reader = cmd.ExecuteReader();

is there somewhere i need to put in my username or password? or is this code just wrong

Many thanks burnside.

-- Edited by longhorn2005

What is your error message?|||

Try the below connection string

SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;Integrated Security=True");

And if your database require Username and password check the below link

Building Connection String C#

HC

|||

Thanks for the replys,

The page seems to crash when i uncomment out the third line of sql code "// SqlDataReader rdr = cmd.ExecuteReader();"
"System.Data.SqlClient" %> and i get this slightly unhelpful error message @.http://s152182516.websitehome.co.uk/test/default2.aspx

<%

@.PageLanguage="C#" %>

<%@.ImportNamespace="System.Data" %>

<%@.ImportNamespace="System.Data.SqlClient" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">protectedvoid Page_Load(object sender,EventArgs e)

{

// Define database connectionSqlConnection conn =newSqlConnection("uid=xxxxx;password=xxxxx;Server=mssql07.oneandone.co.uk;Database=db198704784;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select UserName from Users", conn);SqlDataReader rdr = cmd.ExecuteReader();

}

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><asp:TextBoxID="TextBox1"runat="server"OnTextChanged="TextBox1_TextChanged"Height="176px"Width="338px"></asp:TextBox><asp:LabelID="Label1"runat="server"Text="Test Of SQL"></asp:Label></div></form>

</

body>

</

html>|||

Try to open the connection before you call ExecuteReader() example below

SqlCommand cmd =newSqlCommand("select UserName from Users", conn);

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader();

HC

--

Mark this post as ANSWERED if it helped you

|||

I've tried the Open command but still no joy. I think i might have to hire someone to go into my MS hosting and check it and the SQL is setup, and make a little Logon aspx with the sql database so i can see the code to access it. If anyone is interested please email me.

|||

Hello,

These are some tutorial for you:

http://www.codeproject.com/aspnet/SQLConnect.asp

http://samples.gotdotnet.com/quickstart/aspplus/doc/adoplusoverview.aspx

HTH

|||

I tried to access the page you provided but unfortunately its unavailable. What do you mean by the page crashes? Can you post the complete error you are getting? Make sure the database exists and the table you trying to query exists in thedatabase.

Note: Never give out the username/password in the posts. replace them with XXXXX

Thanks

Basic questions on Service Master Key

Hi. I'm reading about encryption and not totally up-to-speed on what the Service Master Key is all about...

Let me paraphrase this book: Microsoft SQL Server 2005 Implementation and Maintenance Training Kit by Microsoft Press, while I ask questions...
__

The database engine uses the Service Master Key to encrypt...
- Linked server passwords (example?)
- Connection Strings (example?)
- Account credentials (example?)
- All database master keys

You should back up the Service Master Key and store it offsite.
(Even if I never plan to use encryption?)

ALTER SERVICE MASTER KEY REGENERATE regenerates the service master key.
(Is it building a completely different key, or regenerating the original?)
(Why would I want to regenerate it?)
(Does this void all database master keys encrypted by the original Service Master Key?)
(If so, do I regenerate all database master keys voided by the regeneration?)

A database master key is optional.
(So if I have Server A with encrypted database A, that I want to restore to Server B,
I need the following items to perform restore?
- a backup of database A
- the Service Master Key of A at time of backup
- associated password of Service Master Key at time of backup)

Just repeating what it is mentioned in BOL:

The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

Regenerating or restoring the Service Master Key involves decrypting and re-encrypting the complete encryption hierarchy. Unless the key has been compromised, this resource-intensive operation should be scheduled during a period of low demand.

Also http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx fyi for more information.

|||

A few corrections:

The Service Master Key (SMK) has two encryptions, both made using DPAPI - one uses the service account credentials, the other one uses the machine credentials. Note that neither of these encryptions is necessarily protecting the SMK from a machine administrator.

Regenerating the SMK will only require re-encrypting the entities encrypted by it: linked server login passwords (the ones you specify when you set up a linked server), credentials (as created by CREATE CREDENTIAL), and database master keys.

Coming back to the original questions:

It is not necessary to backup the SMK if you do not plan to use it.

REGENERATE creates a new key. It can be used as part of a key rotation policy or if you have reason to believe the old key was compromised. You can also use this to generate a new key if the old one was corrupted and you have no backup around. Regenerating the SMK doesn't invalidate any database master key (DbMK) - it just re-encrypts them with the new key.

For restoring a database to another server, see last paragraphs of http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx. You don't need to move the SMK to the new server, you just need to open and reencrypt the DbMK with the new server's SMK, and you only need to do this if you had the same setup on the original server.

Thanks
Laurentiu

|||

Thanks for taking the time to explain some concepts. I haven't had any experience with encryption, and now feel further behind with SQL 2005's new concepts.

I'm having problems understanding the extent the SMK is capable of.... you wrote about the SMK being compromised, but I don't see how you could compromise it, or what you could do with it once you have it?

- You have to be a SysAdmin to restore a SMK
- You would somehow have to get the SMK backup file onto the server
- You would have to have local access to the SMK backup file to restore it
- You would have to know the password of the restored SMK

And even if all that occurs:
- The linked servers all still show "********" for a password
- You can't decrypt data with a SMK (can you?)
- You can't decrypt a Database Master Key with the SMK (can you?)

|||

The SMK allows you to decrypt all that is encrypted beginning with it - database master keys, initially, then anything encrypted by the database master keys, and so on, towards the data itself.

As you've noticed, there are good protections set up around accessing the SMK, so my mention of "compromising" is more theoretical than practical - you could "loose" the SMK by loosing an SMK backup and the password protecting it, for example.

Having the SMK and a copy of a database with encrypted data (where the encryption protection is rooted at the SMK) allows you to decrypt the data.

Have a look at the encryption hierarchy in Books Online, to understand what key protects what information. The main point is that this is a hierarchy, so the key at its root unlocks everything below. You can detach protection of some data from this hierarchy by using password encryption for certain keys, which uses TripleDES encryption to protect those keys.

http://msdn2.microsoft.com/en-us/library/ms189586.aspx

Also listen to the presentation of encryption in SQL Server 2005 that Rob Walters gave at PASS this year. You can access Rob's presentation here:

http://cmcgc.com/Media/WMP/261115/

Also, there were no encryption features in SQL Server 2000. These features have been introduced in SQL Server 2005.

Thanks
Laurentiu