Posted on Leave a comment

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:vaultkeysjoebob_public.gpg”
secretAliasId=“-4553241976692078076”
                     secretKeyRingFileName=“C:vaultkeysmy_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:vaultkeysjoebob_public.gpg”
                     secretKeyRingFileName=“C:vaultkeysjoebob_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
Posted on Leave a comment

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://servernameinstancename: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

Posted on Leave a comment

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

Posted on Leave a comment

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


Posted on Leave a comment

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

Posted on Leave a comment

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

Posted on Leave a comment

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