More Thoughts on Visual Studio Team Edition for Database Professionals

by | Oct 20, 2006 | BizTalk Community Blogs via Syndication

Wow! Judging from the number of hits on my last post regarding the new Visual Studio Team Edition for Database Professionals, there must be a lot of interest in this upcoming product! So I'm gonna dive right in with another post and give you some additional ideas on how this cool new product can really change the way you look at database development and the database development lifecycle!

What is the Database Development Lifecycle?

If you've developed any serious corporate or personal .NET applications in the past, you know that the development never really ends and that ALL development is an iterative process. This is especially true for the databases developed for your applications, since these databases continue to grow and change as your application is put to use, modifications are made and features are extended. I find myself tweaking tables, indexes, user defined functions and especially stored procedures more and more as my corporate applications mature and grow. This is the typical database development lifecycle.

So How Does Visual Studio Team Edition for Database Professionals Change This?

Working Off-Line

I've found the single biggest change (paradigm shift) that "TeamData" enforces is working "off-line" in a "sandbox" environment rather than working on-line like you do using the Query Analyzer or SQL Management Studio. This "reality shift" can really take some getting used to and I suspect it will be the main issue for most developers at first. However, once you get used to the idea of NOT MUCKING AROUND with a live database (sorry for shouting) it really becomes second nature and provides a much greater sense of control during database development. The only downside of this is that the new SQL Editor included with "TeamData" does not (yet) provide Intellisense for T-SQL so you'll need to brush up on your T-SQL programming skills and have the SQL Books Online (docs) open when you're writing T-SQL code.

Unit Testing & Data Generation

The second largest change is that "TeamData" provides the ability to unit test SQL user defined functions and stored procedures just like we can with our C# (or VB.NET) code. I believe this feature more than makes up for the lack of Intellisense in the SQL Editor since we can now test our code in a much more thorough and controlled manner. I can't tell you how many times in the past that I "thought" I had thoroughly tested a sproc only to have a user find an obvious error that a simple unit test would have uncovered during development. For those of you concerned about data privacy and sensitivity (if you're not, you should be), "TeamData" also includes an awesome data generation feature so that you can test representative data without using your actual (sensitive) data.

Schema and Data Comparison

This one's my favorite and after using Red Gate's tools for some time now, I'm very very impressed with how "TeamData" handles these vital tasks. If your development team is anything like ours, not everyone likes to work in the same manner and some developers will still want to use the Query Analyzer and SQL Management Studio to "tweak" your development or staging databases from time to time. Using the "TeamData" Schema and Data Compare features you can keep your data projects "in sync" with your development databases even if not all the members of your team are using the same tools! This is really vital until you can convince ALL your developers to work "off-line" in a "sandbox" environment, unit test their changes, build them for verification and check them into Team Foundation Server's source control.

Figure 1: Schema Compare

Build

The final and possibly most important paradigm shift that "TeamData" brings to database development is the concept of the BUILD. While not Earth shattering to most .NET developers, the concept of running a regular build cycle for database development can seem completely foreign to most database developers, who like me, were used to doing these things on an "ad-hoc" basis. Believe me, hand your dba a well written and fully verified build script and he'll fall over in a dead faint!

Source Control

One last thought! In the past, I never had a good way to store my SQL source code. I hate VSS (and I'm not alone) and storing SQL scripts on a file system makes maintenance a nightmare. Now I use "TeamData" and Team Foundation Server and I'm sleeping a lot better at night knowing that my SQL databases are under source control.

Some Conclusions

I've been really fortunate to be working as a "TeamData" TAP (Technology Access Program) customer for the past few months. I've gotten to know some of the "TeamData" Program Managers, Product Managers and Developers along the way. This team has been one of the most open, listening and responsive teams I've ever worked with at Microsoft and I truly believe this new product will change the way you look at database development.

It won't be easy. It will challenge many of your current database development beliefs. It will force you to look at how you currently do database development and you may not like what you see. I know I didn't! But in the end, this product can really enhance your database development skills, make you a more professional developer and put a sparkle in your dba's eyes!

As always, your comments are encouraged and welcome! Don't be shy. Tell me what you think!

Technorati Tags: Visual Studio, Team Foundation Server, DataDude, TeamData

Share this post: Email it! | bookmark it! | digg it! | reddit!| kick it!

WCF Oracle Application Server WS-Security interoperability Part1: from WCF to Oracle

by | Oct 20, 2006 | BizTalk Community Blogs via Syndication

By Jesus Rodriguez

This article is part of a series intended to explore interesting Web services interoperability scenarios between Microsoft .NET technologies and Oracle Application Server. The first two articles of this series explored interoperability scenarios  (WS-Security interoperability and WS-Addressing interoperability) between Oracle Business Process Execution Language (BPEL) Process Manager (PM) and Microsoft Web Services Enhancements (WSE) and Windows Communication Foundation (WCF) platforms.

The articles that compose this series are intended to illustrate techniques, architecture and design strategies in order to address some of the most common scenarios in Web services interoperability between Oracle App Server and Microsoft Web Services platforms. Particularly, this article is focused on how to implement the Anonymous over Certificate WS-Security scenario between Oracle App Server and Microsoft WCF.

What is WS-Security?

WS-Security is the main specification to enable security in Web services. It addresses scenarios like message integrity, authentication and message confidentiality.

WS-Security 1.1 was recently approved as an OASIS Standard.

Is there something wrong with SSL?

Message Security vs. Transport Security

Secure Sockets Layer (SSL) has proven its effectiveness in securing resources through the Internet. When dealing with Web services however, SSL must be considered in conjunction with WS-Security to provide a truly secure environment.

  • SSL is strictly tied to TCP-based transports like HTTP; for Web services that are using non-TCP transports such as MSMQ or SMTP, SSL does not represent a viable option.
  • SSL was designed for point-to-point communications; making it difficult to implement in a routing scenario between Web services. In a routing scenario, the final receiver must be able to validate the original credentials.  Extensibility using SSL becomes difficult to provide when those credentials are not stored in the message.
  • SSL protects the messages on the wire (between the endpoints) but does not provide protection for the message on the endpoints; allowing access to the whole message upon gaining access to one of the endpoints.

Anonymous over Certificate scenario

This is one of the most common scenarios when it comes to secure interactions between Web services. Basically, Anonymous over Certificate represents a case in which the Web service and the client trust each other. A good example could be an ATM machine (client) and the Bank (service). Even though trust relationships exists between the client and the service, it is still required that the interaction between them be encrypted and/or signed.     In addition, all security must be implemented using a message-based security approach.

The following section will illustrate how to implement an Anonymous over Certificate scenario with a WCF client consuming an Oracle Application Server Web Service.

The implementation: From WCF to Oracle App Server.

Oracle Web Service

Our target Web service for this example implements a single operation as shown in the following figure.

public class MathWS {

                    public MathWS() {

                    }

                   

                    public int Add(int param1, int param2)

                    {

                              return param1 + param2;

                    }

}

 In order to implement Anonymous over Certificate scenario we need to add

WS-Security configuration properties as is illustrated in the following figure.

 

Figure 1: WS-Security settings dialog in Oracle JDeveloper

After completing the dialog, the WS-Security configuration properties should look like the following.

<oracle-Webservices xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                    xsi:noNamespaceSchemaLocation="http://xmlns.oracle.com/oracleas/schema/oracle-Webservices-10_0.xsd">
    Web Service Description elements…
    </Webservice-description>
    <Webservice-description name="MathWS">
        <port-component name="MathWSSoap12HttpPort">
            <runtime enabled="security">
                <security>
                    <key-store name="" store-pass="my password" path="my certificate store…"/>
                    <signature-key key-pass="my password" alias="signature key"/>
                    <encryption-key key-pass="my password" alias="encryption key"/>
                    <inbound>
                        <verify-signature>
                            <signature-methods>
                                <signature-method>DSA-SHA1</signature-method>
                                <signature-method>RSA-MD5</signature-method>
                                <signature-method>RSA-SHA1</signature-method>
                            </signature-methods>
                            <tbs-elements>
                                <tbs-element local-part="Body"
                                             name-space="http://schemas.xmlsoap.org/soap/envelope/"/>
                            </tbs-elements>
                            <verify-timestamp created="false" expiry="28800"/>
                        </verify-signature>
                        <decrypt>
                            <encryption-methods>
                                <encryption-method>AES-128</encryption-method>
                                <encryption-method>AES-256</encryption-method>
                                <encryption-method>3DES</encryption-method>
                            </encryption-methods>
                            <tbe-elements>
                                <tbe-element local-part="Body"
                                             name-space="http://schemas.xmlsoap.org/soap/envelope/"
                                             mode="CONTENT"/>
                            </tbe-elements>
                        </decrypt>
                    </inbound>
                    <outbound>
                        <signature>
                            <signature-method>RSA-SHA1</signature-method>
                            <tbs-elements>
                                <tbs-element local-part="Body"
                                             name-space="http://schemas.xmlsoap.org/soap/envelope/"/>
                            </tbs-elements>
                            <add-timestamp created="false" expiry="28800"/>
                        </signature>
                        <encrypt>
                            <use-request-cert>true</use-request-cert>
                            <encryption-method>3DES</encryption-method>
                            <keytransport-method>RSA-1_5</keytransport-method>
                            <tbe-elements>
                                <tbe-element local-part="Body"
                                             name-space="http://schemas.xmlsoap.org/soap/envelope/"
                                             mode="CONTENT"/>
                            </tbe-elements>
                        </encrypt>
                    </outbound>
                </security>
            </runtime>
            <operations>
                <operation name="Add" input="{http://wsprj/}AddElement"/>
            </operations>
        </port-component>
    </Webservice-description>
</oracle-Webservices>

As you can see in the highlighted section, this Web service is configured to accept encrypted and signed SOAP messages using certificates. The certificates used in this procedure should be stored in an Oracle compatible certificate store. To find out more information about managing Oracle certificate stores, read Administering Web Services Security in the Oracle App Server documentation.

Those are all the steps required to implement the Anonymous over Certificate security pattern in Oracle Application Server. Now it is time to create a client that consumes this Web service.

WCF client

The fist step required to consume the Web service explained in the previous section using WCF is to generate the Web service proxy. This can either be done by adding a Service Reference to the client project or using the Service Metadata tool (SvcUtil.exe). Another required step is importing the required certificates used for signing and encryption in the Windows certificate store.

In order to implement Anonymous over Certificate scenario the client binding needs to include message security settings. Client authentication is not required so that the clientCredentialType setting must be set to None. Given that Oracle App Server does not implement WS-Trust; key exchange settings needs to be disabled. The following configuration file illustrates those concepts.

 

<configuration>

      <system.serviceModel>

 

            <client>

                  <endpoint name="Username"

                                            address="Oracle WS Url…"

                                            binding="wsHttpBinding"

                                            bindingConfiguration="Binding1"

                                            behaviorConfiguration="ClientCertificateBehavior"

                                            contract="MathWebService">

                        …

                  </endpoint>

            </client>

 

            <bindings>

                  <wsHttpBinding>

                        <binding name="Binding1">

                              <security mode="Message">

                                    <message clientCredentialType="None" algorithmSuite="Basic256" negotiateServiceCredential="false" establishSecurityContext="false"  />

                              </security>

                        </binding>

 

                  </wsHttpBinding>

            </bindings>

            <behaviors>

                  <endpointBehaviors>

                        <behavior name="ClientCertificateBehavior">

                              <clientCredentials>

                                    <serviceCertificate>

                                          <defaultCertificate storeLocation="LocalMachine" storeName="Root" findValue="my certificaste…" x509FindType="FindBySubjectName" />

                                    </serviceCertificate>

                              </clientCredentials>

                        </behavior>

                  </endpointBehaviors>

            </behaviors>

      </system.serviceModel>

</configuration>

The following code shows how to invoke the target Web service from the WCF client application.

MathWebServiceClient proxy = new MathWebServiceClient();

AddRequest request = new AddRequest();

request.param1 = 34;

request.param2 = 45;

int result= proxy.Add(request);

When this client code runs it produces the following WS-Security request.

 

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">

      <s:Header>

            <a:Action s:mustUnderstand="1" u:Id="_4">http://wsprj//Add</a:Action>

            <a:MessageID u:Id="_5">urn:uuid:da788d69-7b2f-4da0-b4a2-462aa3e27034</a:MessageID>

            <a:ReplyTo u:Id="_6">

                  <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>

            </a:ReplyTo>

            <a:To s:mustUnderstand="1" u:Id="_7">Web Service Url…</a:To>

            <o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">

                  <u:Timestamp u:Id="uuid-9f1b2072-3a2f-42ab-b13a-042a910d2c46-2">

                        <u:Created>2006-10-14T00:45:56.125Z</u:Created>

                        <u:Expires>2006-10-14T00:50:56.125Z</u:Expires>

                  </u:Timestamp>

                  <e:EncryptedKey Id="uuid-9f1b2072-3a2f-42ab-b13a-042a910d2c46-1" xmlns:e="http://www.w3.org/2001/04/xmlenc#">

                        <e:EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-oaep-mgf1p">

                              <DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1" xmlns="http://www.w3.org/2000/09/xmldsig#"/>

                        </e:EncryptionMethod>

                        <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">

                              <o:SecurityTokenReference>

                                    <o:KeyIdentifier ValueType="http://docs.oasis-open.org/wss/oasis-wss-soap-message-security-1.1#ThumbprintSHA1" EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">yn+OeSsl5gFf7Kcu6sCJdE6NQSA=</o:KeyIdentifier>

                              </o:SecurityTokenReference>

                        </KeyInfo>

                        <e:CipherData>

      <e:CipherValue>Encrypted Data…</e:CipherValue>

                        </e:CipherData>

                  </e:EncryptedKey>

                  <c:DerivedKeyToken u:Id="_0" xmlns:c="http://schemas.xmlsoap.org/ws/2005/02/sc">

                        <o:SecurityTokenReference>

                              <o:Reference ValueType="http://docs.oasis-open.org/wss/oasis-wss-soap-message-security-1.1#EncryptedKey" URI="#uuid-9f1b2072-3a2f-42ab-b13a-042a910d2c46-1"/>

                        </o:SecurityTokenReference>

                        <c:Offset>0</c:Offset>

                        <c:Length>24</c:Length>

                        <c:Nonce>yx51fwZgkyMOiJx6oi1Syg==</c:Nonce>

                  </c:DerivedKeyToken>

                  <c:DerivedKeyToken u:Id="_1" xmlns:c="http://schemas.xmlsoap.org/ws/2005/02/sc">

                        <o:SecurityTokenReference>

                              <o:Reference ValueType="http://docs.oasis-open.org/wss/oasis-wss-soap-message-security-1.1#EncryptedKey" URI="#uuid-9f1b2072-3a2f-42ab-b13a-042a910d2c46-1"/>

                        </o:SecurityTokenReference>

                        <c:Nonce>tlSDkdNGRsMliLbV+Lgcuw==</c:Nonce>

                  </c:DerivedKeyToken>

                  <e:ReferenceList xmlns:e="http://www.w3.org/2001/04/xmlenc#">

                        <e:DataReference URI="#_3"/>

                        <e:DataReference URI="#_8"/>

                  </e:ReferenceList>

                  <e:EncryptedData Id="_8" Type="http://www.w3.org/2001/04/xmlenc#Element" xmlns:e="http://www.w3.org/2001/04/xmlenc#">

                        <e:EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#aes256-cbc"/>

                        <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">

                              <o:SecurityTokenReference>

                                    <o:Reference URI="#_1"/>

                              </o:SecurityTokenReference>

                        </KeyInfo>

                        <e:CipherData>

                              <e:CipherValue>Encrypted data….</e:CipherValue>

                        </e:CipherData>

                  </e:EncryptedData>

            </o:Security>

      </s:Header>

      <s:Body u:Id="_2">

            <e:EncryptedData Id="_3" Type="http://www.w3.org/2001/04/xmlenc#Content" xmlns:e="http://www.w3.org/2001/04/xmlenc#">

                  <e:EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#aes256-cbc"/>

                  <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">

                        <o:SecurityTokenReference xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">

                              <o:Reference URI="#_1"/>

                        </o:SecurityTokenReference>

                  </KeyInfo>

                  <e:CipherData>

                        <e:CipherValue>Encrypted data…</e:CipherValue>

                  </e:CipherData>

            </e:EncryptedData>

      </s:Body>

</s:Envelope>

   

Conclusions

This article explained the techniques used to implement the Anonymous over Certificate WS-Security scenario between WCF and Oracle Application Server. Specifically, it covered how to invoke an Oracle Application Server Web Service that implements Anonymous over Certificate from WCF. The second part of this article will address this scenario from Oracle Application Server to WCF.

Share this post: Email it! | bookmark it! | digg it! | reddit!

Some Links To BizTalk 2006 Performance

by | Oct 20, 2006 | BizTalk Community Blogs via Syndication


With BTS2006 in full force, the BTS team is frequently fielding performance related questions. There is a lot of information already available for customers to reference; the product documentation is a great place to start.


The Performance and Capacity section in the core docs covers a wide range of topics. The 64-Bit Support article delves into components of BizTalk that are supported in 64 bit and also answers some basic FAQ regarding this topic.


There is a section that covers planning for sustained performance.  It contains articles that provide useful guidance on how to include performance considerations during the design, implementation and release phases of BTS2K6 design. It also highlights how to set realistic performance goals and how to measure them. The documentation also contains guidance on scaling solutions as well as tips and tricks to improve performance.  In addition to this, there is a paper available on how to manage a successful performance lab here.


Once a solution is deployed, there are also articles in msdn that cover the performance counters available in BizTalk to detect bottlenecks.


And if you just want to look at hard numbers, a comparative adapter study compares the performance of BizTalk Server 2004 against BizTalk Server 2006 adapters.

Resolved: BizTalk 2006 SAP Adapter schema generation error.

Resolved: BizTalk 2006 SAP Adapter schema generation error.

by | Oct 19, 2006 | BizTalk Community Blogs via Syndication

Got an email today from one of our BizTalk customers in Melbourne. He seemed to be having an error while trying to retrieve an IDOC  from SAP using the BizTalk 2006 SAP Adapter. The error he was getting was:

Anyway, after doing a bit of digging around, and some quick testing from the customer, it was found that the account that they were using to connect to SAP and retrieve the schema definitions did not have sufficient permissions! Though I would post this for the rest of the BizTalk world and thanks so much to my customer for allowing me to share (you know who you are;-)

Scott Colestock’s Deployment Framework for BizTalk

by | Oct 19, 2006 | BizTalk Community Blogs via Syndication

Today I had the pleasure of finally integrating the BizTalk solution
at my current client site into the Deployment
Framework for BizTalk created by Scott Colestock.  They are still using BizTalk
Server 2004 so the process began with downloading the 2004 version of the Framework
from Scott’s site and getting the latest version of NAnt and NAntContrib.

Overall I’ve always considered the Deployment Framework to be a joy to work with,
but this is the first time I’ve had to retro-fit it into such an extensive existing
solution.  Some of the things which I encountered follow as reminders of gotchas
in such an endeavor.

  • It is not mentioned in the documentation, but Scott’s framework has an “additionalComponents”
    property which deploys pre-compiled assemblys from the Deploy Tools directory. 
    My client was already using log4net, but a newer version than what ships with the
    framework and I had to override this property so it didn’t deploy two versions of
    log4net to the GAC.
  • Remember that any Visual Basic .NET assemblies need to have their Build configurations
    changed to compile to bin\debug and bin\release.  By default VB.NET has one location
    “bin” which holds whatever you compiled last.
  • When retrofitting your existing bindings file to use XmlPreProcessor to swap out local
    environment settings, I found that CodeRush from DevExpress saved the day.  It
    allowed me to create a simple expansion template which took whatever was on my clipboard
    and inserted it twice into the structure of the expension, once inside the PreProcessor
    directive, once outside.
  • If you are working with alot of directly linked receive and send ports, then you’ll
    want to modify the XPaths file in deploy tools to encode the Filter tag for ports
    as well as all the other tags it does already.  I submitted this back to Scott
    who graciously thanked me for catching that and said he’d include it in the next 2006
    release, and in 2004 if he ever did another release of that version.
  • You’ll definitely want to look at this
    article by Scott which discusses how to add additional HAT queries to the deploy
    framework as well.

BizTalk 2006 Performance Metrics With SQL 2005

by | Oct 19, 2006 | BizTalk Community Blogs via Syndication


Useful report released by our partner Infosys showing performance improvements using SQL Server 2005 with BizTalk 2006 instead of SQL Server 2000. I like how they explain the scenario they tested, show the results with BizTalk 2004 and SQL 2000, BizTalk 2006 and SQL 2000, and then BizTalk 2006 and SQL 2005. I also appreciate their commentary as to *why* the BTS06 + SQL05 combo performed better.


One number you’ll see a lot here is a 40% improvement in performance with BTS06 + SQL05. You’ll find scenarios such as interchange (batch) processing, executing rules from orchestration, atomic transactions, convoys, and more. Don’t miss the section on “backlogs” where you see how SQL 2005 significantly improves processing of queued up messages. Good stuff.


Technorati Tags: BizTalk

On my way to Norway

On my way to Norway

by | Oct 19, 2006 | BizTalk Community Blogs via Syndication

To teach a Windows Workflow Foundation 5-day course.  Everyone says it will be
beautiful.

 BTW – I don’t plan on updating the Atlas Workflow Designer until they release
the next version of Atlas – read Biertand Roy’s blog about why –

Link to Atlas and more 
– they are about to change from closures to prototypes.  This will be a great
chance for me to re-architect the whole thing.  The next big feature will be
HTML based rule editing.