Example PGP encryption and decryption using MuleStudio 3.5

PGP_PublicEncrypter – Global Encryption Element

                      

In this case we are using our Secret Key, Secret Alias ID, and Secret Passphrase to sign the encrypted message.   We use the recipient’s (in this case “Joe Bob”) Public Key and Principal Email.

PGP_PrivateDecrypter – Global Encryption Element

                       

Global Elements:

    <encryption:config name="PGP_PublicEncrypter"

defaultEncrypter="PGP_ENCRYPTER"

                     doc:name="PGP_PublicEncrypter">

        <encryption:pgp-encrypter-config

                     principal="Joe Bob &lt;jobbob@yahoo.com&gt;"

publicKeyRingFileName="C:\vault\keys\joebob_public.gpg"

secretAliasId="-4553241976692078076"

                     secretKeyRingFileName="C:\vault\keys\my_secret.gpg"

                     secretPassphrase="MySecretPass"/>

    </encryption:config>

   

    <encryption:config name="PGP_PrivateDecrypter"

defaultEncrypter="PGP_ENCRYPTER"

                     doc:name="PGP_PrivateDecrypter">

        <encryption:pgp-encrypter-config

principal="Joe Bob &lt;jobbob@yahoo.com&gt;"

publicKeyRingFileName="C:\vault\keys\joebob_public.gpg"

                     secretKeyRingFileName="C:\vault\keys\joebob_private.gpg"

                     secretAliasId="-5394156070371012997"

                     secretPassphrase="joebob12" />

    </encryption:config>

Flow Elements:

<flow name="flowEncrypt" doc:name="flowEncrypt">

<vm:inbound-endpoint exchange-pattern="request-response" path="queueEncrypt"

       doc:name="vmEncrypt"/>

       

<logger message="#[payload]" level="INFO" category="### INPUT LOGGER ###"

       doc:name="Logger"/>

<encryption:encrypt using="PGP_ENCRYPTER" config-ref="PGP_PublicEncrypter"

       doc:name="Encrypter"/>

       

<logger message="#[payload]" level="INFO" category="### ENCRYPTED LOGGER ###"

       doc:name="Logger"/>

       

<encryption:decrypt using="PGP_ENCRYPTER" config-ref="PGP_PrivateDecrypter"

doc:name="Decrypter"/>

       

<logger message="#[payload]" level="INFO" category="### DECRYPTED LOGGER ###"

       doc:name="Logger"/>

</flow>

------------------------------------------------------

./src/test/java/EncryptFlowTest.java

------------------------------------------------------

import static org.junit.Assert.assertEquals;

import static org.junit.Assert.assertNotNull;

import static org.junit.Assert.assertTrue;

import java.io.File;

import java.io.IOException;

import java.util.Collection;

import java.util.HashMap;

import java.util.Map;

import org.apache.commons.io.FileUtils;

import org.junit.Test;

import org.mule.DefaultMuleMessage;

import org.mule.api.MuleException;

import org.mule.api.MuleMessage;

import org.mule.api.client.MuleClient;

import org.mule.api.transport.PropertyScope;

import org.mule.tck.junit4.FunctionalTestCase;

public class EncryptFlowTest extends FunctionalTestCase

{

   @Test

   public void testEncryptFlow () throws MuleException

   {

         HashMap<String, Object> propsMap = new HashMap<String, Object>();  

        

      MuleClient client = muleContext.getClient();

      String payloadSend = new String("The quick brown fox jumped over the lazy dog");

      MuleMessage reply = client.send ("vm://queueEncrypt", payloadSend, propsMap, 5000);

      assertNotNull(reply);

      assertNotNull(reply.getPayload());

      assertTrue(reply.getPayload() instanceof String );

      String result = (String)reply.getPayload();

      assertEquals(result, payloadSend);

   }

  

  

   @Override

   protected String getConfigResources()

   {

      return "src/main/app/pgpexample.xml";

   }

}

------------------------------------------------------

./src/test/resources/log4j.properties

------------------------------------------------------

# Default log level

log4j.rootCategory=INFO, console

log4j.appender.console=org.apache.log4j.ConsoleAppender

log4j.appender.console.layout=org.apache.log4j.PatternLayout

log4j.appender.console.layout.ConversionPattern=%-5p %d [%t] %c: %m%n

################################################

# You can set custom log levels per-package here

################################################

# Reduce noise for Mule High Availability

log4j.logger.com.gigaspaces=ERROR

log4j.logger.com.j_spaces=ERROR

log4j.logger.com.sun.jini=ERROR

log4j.logger.net.jini=ERROR

# CXF is used heavily by Mule for web services

log4j.logger.org.apache.cxf=WARN

# Apache Commons tend to make a lot of noise which can clutter the log.

log4j.logger.org.apache=WARN

# Reduce startup noise

log4j.logger.org.springframework.beans.factory=WARN

# Mule classes

log4j.logger.org.mule=INFO

log4j.logger.com.mulesoft=INFO

# Your custom classes

log4j.logger.com.mycompany=DEBUG

Adding SQL Server JDBC Connector in Mulesoft

1. Microsoft JDBC Driver for SQL Server can be downloaded here: http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx

2. Add theSQL Server JDBC connector to your project: 

Project -->Build Path --> Add External Archives...

3. In the connections explorer add a new connection global element for data source by selecting MS SQL Data Source

4. Fill in connection information.  JDBC urls for SQL Server should be of form: 

For default instance on default port 1433

jdbc:sqlserver://servername:1433;databaseName=databasename

For named instance with non-default port:

jdbc:sqlserver://servername\instancename:port;databaseName=databasename

5. Now create database connection global elment and select the sql server database connection defined in steps 

Note that the SQL Server Browser service may need to be active for JDBC to see the server.

Additional info and Mulesoft tutorial:

http://www.mulesoft.org/connectors/microsoft-sql-server-connector

which port is which?

Depending how the SQL Server instance is set up, it may be a named instance and not have a standard port of 1433.

To find out which port, you can run netstat in a command prompt.

Another way is to open SQL Server Configuration Manager and browse to:

SQL Server Network Configuration -> Protocols for <SERVERNAME> -> TCP/IP

Then in the TCP/IP window select the IP Address tab and scroll down to IPAll

Enable Alert system for SQL Server Agent

DatabaseMail on the SQL Server Agent -> Properties -> Alert System (check the checkbox), and then restart SQL Server Agent

Very Cool Tools

http://www.brentozar.com/first-aid/downloads/

Clearing transaction log

--First check for open transactions
DBCC OPENTRAN
GO


ALTER DATABASE [databasename] set recovery simple 
GO

CHECKPOINT
GO

DBCC SHRINKFILE ([transactionlog],1)
GO

ALTER DATABASE [databasename] set recovery full
GO

--Since SQL Server lost the ability to TRUNCATE_ONLY in 2008
--here's an alternative to setting backup mode to SIMPLE, then checkpoint.

BACKUP LOG [databasename] TO DISK=’NULL’
GO

fnTitleCase

CREATE FUNCTION [dbo].[fnTitleCase]( @text AS varchar(8000) )
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @Reset bit;
   DECLARE @Ret varchar(8000);
   DECLARE @i int;
   DECLARE @c char(1);

   SELECT @Reset = 1, @i=1, @Ret = '';

   WHILE (@i <= len(@Text))
    BEGIN
       SELECT @c= substring(@Text,@i,1),
                  @Ret = @Ret + case when @Reset=1 then UPPER(@c) 
                                                   else LOWER(@c) end,
                  @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
                  @i = @i +1
    END
   RETURN @Ret
END

To move a table from one filegroup to another while the system is "live" and "online", recreate its clustered index...  Be sure the target filegroup is already sized to handle the incoming data :)

ALTER DATABASE databaseName
MODIFY FILE
    (NAME = datafileLogicalName,
    SIZE = 2000MB);
GO

CREATE CLUSTERED INDEX [i_tableName_cu] ON [dbo].[tableName] 
(
[columName] ASC
)
WITH DROP_EXISTING
ON [filegroupName]
GO

To consolidate database files:

DBCC SHRINKFILE('logicalFileName', EMPTYFILE);
ALTER DATABASE databaseName REMOVE FILE logicalFileName;

fnGetVal

ALTER FUNCTION [dbo].[fnGetVal]( @s varchar(8000), @label varchar(1000) )
RETURNS varchar(1000)
AS
BEGIN
   DECLARE @value varchar(1000)
         , @value_start int
         , @value_end int
         
   SET @value_start = charindex(@label,@s)
   
   IF @value_start > 0
    BEGIN
      SET @value_start += len(@label) + 3
      SET @value_end = charindex(',',@s,@value_start)

      IF @value_end > @value_start
       BEGIN
         SET @value = substring(@s,@value_start,@value_end-@value_start)
       END
      ELSE
       BEGIN
         SET @value = substring(@s,@value_start,len(@s)-@value_start+1)
       END
    END

 RETURN ltrim(rtrim(@value))
END

good tools

a good tool will make something you already do, easier

never get a tool expecting that the tool will make you do something you are not already doing...

it is similar to exercise equipment...

if you are not already doing push-ups, sit-ups, and jogging, a gym membership or purchasing gym equipment will not make you start exercising...

space used by tables

;WITH
space_cte AS
(
          SELECT t.NAME "TableName"
               , p.rows "RowCounts"
               , SUM(a.total_pages) * 8 "TotalSpaceKB"
               , SUM(a.used_pages) * 8 "UsedSpaceKB"
               , (SUM(a.total_pages) - SUM(a.used_pages)) * 8 "UnusedSpaceKB"
            FROM sys.tables t
      INNER JOIN sys.indexes i 
              ON t.OBJECT_ID = i.object_id
      INNER JOIN sys.partitions p 
              ON i.object_id = p.OBJECT_ID 
             AND i.index_id = p.index_id
      INNER JOIN sys.allocation_units a 
              ON p.partition_id = a.container_id
           WHERE t.NAME NOT LIKE 'dt%' 
             AND t.is_ms_shipped = 0
             AND i.OBJECT_ID > 255 
        GROUP BY t.Name
               , p.Rows
)
          SELECT A.TableName
               , A.RowCounts
               , A.TotalSpaceKB
               , ( case when A.UsedSpaceKB > 1000000 then cast(A.UsedSpaceKB / 1000000 As varchar(30)) + ' GB'
                        when A.UsedSpaceKB > 1000 then cast(A.UsedSpaceKB / 1000 As varchar(30)) + ' MB'
                        else cast(A.UsedSpaceKB as varchar(30)) + ' KB' end ) "UsedSpace"
               , A.UnusedSpaceKB
            FROM space_cte A
        ORDER BY A.UsedSpaceKB desc

CHECKDB REPAIR_REBUILD

use master
go

DBCC CHECKDB (thedb) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

ALTER DATABASE thedb
   SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
GO

ALTER DATABASE thedb
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB (thedb, REPAIR_REBUILD) WITH ALL_ERRORMSGS;
GO

--DBCC CHECKDB (thedb, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
--GO

ALTER DATABASE thedb
   SET MULTI_USER;
GO

CHECKPOINT;

logical page errors

--Msg 605, Level 21, State 3, Line 13
--Attempt to fetch logical page (1:3418) in database 16 failed. It belongs to allocation unit 25896092997713920 not to 72057594982891520.


dbcc traceon(3604) --3604 = redirect error output to client instead of log
dbcc page(16,1,3418,1)  --dbid, fileid, pageid, level 0-3
dbcc traceoff(3604)

dbcc traceon/traceoff

dbcc traceon(302) redirects output to client rather than logs.  dbcc traceon(302) is often used in conjunction with dbcc traceon(310), which provides more detail on the optimizer’s join order decisions and final cost estimates. dbcc traceon(310) also prints a “Final plan” block at the end of query optimization. To enable this trace option also, use:

dbcc traceon(3604, 302, 310)

To turn off the output, use:

dbcc traceoff(3604, 302, 310)

DBCC CHECKDB()

exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, NO_INFOMSGS'

Connect to SQL Server When System Administrators Are Locked Out

http://msdn.microsoft.com/en-us/library/dd207004(v=sql.105).aspx


How to: Change Server Authentication Mode

http://msdn.microsoft.com/en-us/library/ms188670(v=SQL.105).aspx


these articles came in handy today...

Rowcount all tables

declare @tabs TABLE ( id int identity(1,1), tabname sysname)
declare @res TABLE ( tabname sysname, numrows int )

declare @id int, @numrows int, @tabname sysname, @sql nvarchar(4000)

insert into @tabs (tabname)
select name from sysobjects where type = 'U' order by 1 asc

select @id = max(id) from @tabs

while @id > 0
 begin
   select @tabname = tabname from @tabs where id = @id
   set @sql = 'select ''' + @tabname + ''' "tabname", count(1) "numrows" from [' + @tabname + ']'

   insert into @res ( tabname, numrows )
   exec sp_executesql @sql

   set @id = @id - 1
 end

 select tabname, numrows from @res where numrows > 0 order by tabname

remap db user to server login

When you restore a 2005+ database from one instance to another, the database users are preserved in the database, but now they are linked to the GUID of the login on the originating instance.   The login may exist by the same name on the target server, but since the principal GUIDs are different the user is not linked to that login.  One possibility is to drop and recreate the user and/or login, but this is very destructive in the case that there are complex permissions involved for the user in the DB.

   DECLARE @tab TABLE ( id int identity(1,1), uname sysname )

   DECLARE @id int
         , @uname sysname
         , @sql nvarchar(4000)

     INSERT INTO @tab ( uname )
          SELECT name
            FROM sysusers 
           WHERE issqluser = 1 
             AND hasdbaccess = 1 
             AND name != 'dbo'

          SELECT @id = max(id) from @tab

   WHILE @id > 0
    BEGIN
          SELECT @uname = uname 
            FROM @tab 
           WHERE id = @id

      EXEC sp_change_users_login @action = 'Update_One', @UserNamePattern = @uname, @LoginName = @uname

      SET @id = @id - 1
    END

 Used to use sp_change_users_login, but since that is now deprecated, it is recommended to use the following:

alter user [joeuser] with login = [joeuser]

Truncation error in job

Had a real winner this week, had some stored procs that would run fine in SSMS, but would return error 8152 String or binary  data would be truncated when run as a job.
Turns out the table had a field for updated by that was defaulted to suser_name() but was only 20 chars long.   The user name the job ran as was almost 40 chars.
Wont say how much time was spent tracking this one down :),

Error: The maximum string content length quota (8192) has been exceeded while reading XML data. Solution: Adjust buffer, message, and string content length in client's app config or web config.

<configuration>

...

 <system .servicemodel=".servicemodel">
  <bindings>
    <basichttpbinding>
       <binding ...="..." maxbuffersize="20481000" maxreceivedmessagesize="20481000" name="WSSoap">
         <readerquotas ...="..." maxstringcontentlength="20481000">
         </readerquotas>
       </binding>
    </basichttpbinding>
  </bindings>
 </system>
</configuration>

Example using Soap Header username & password

Example of a minimal implementation that includes Soap username and password. First create the service:

-------------------------- websvc.cs ----------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Services;
using System.Web.Services.Protocols;

namespace wstest
{
   [WebService(Namespace = "http://tempuri.org/")]
   [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
   [System.ComponentModel.ToolboxItem(false)]
   public class Service1 : System.Web.Services.WebService
   {
      public ServiceAuthHeader CustomSoapHeader;

      [WebMethod]
      [SoapHeader("CustomSoapHeader")]
      public string HelloWorld()
      {
         ServiceAuthHeaderValidation.Validate(CustomSoapHeader);
         return "Hello World";
      }
   }


   public class ServiceAuthHeader : SoapHeader
   {
      public string Username;
      public string Password;
   }
 
 
   public class ServiceAuthHeaderValidation
   {
      public static bool Validate(ServiceAuthHeader soapHeader)
      {
         if (soapHeader == null)
         {
            throw new NullReferenceException("No soap header was specified.");
         }
         else if (soapHeader.Username == null || soapHeader.Password == null)
         {
            throw new NullReferenceException("Username and password are required.");
         }
         else if (soapHeader.Username != "myuser" || soapHeader.Password != "mypass")
         {
            throw new NullReferenceException("Provide correct values for username and password.");
         }
         return true;
      }
   }
   
}

-------------------------- websvc.cs ----------------------

Launch the service, create a console app, right click "Service References" and select "Add Service Reference", paste url to WSDL for web service.

-------------------------- client.cs ----------------------

using System;
using System.Collections.Generic;

namespace ConsoleApplication2
{
   class Program
   {
      static void Main(string[] args)
      {
         string s;
         ServiceReference1.Service1SoapClient svc = new ServiceReference1.Service1SoapClient();
         ServiceReference1.ServiceAuthHeader hdr = new ServiceReference1.ServiceAuthHeader();
         hdr.Username = "myuser";
         hdr.Password = "mypass";
         s = svc.HelloWorld(hdr);
         Console.WriteLine(s);
      }
   }
}

-------------------------- client.cs ----------------------