Developing and configuring a custom password credential validator [PCV] for PingFederate

This post provides a step-by-step instructions on developing and configuring  a custom password credential validator [PCV] for PingFederate using PingFederate SDK.
If you are using PingFederate in your enterprise, you would probably use an authentication service from PingFederate to authenticate your users. This sample example of custom PCV, demonstrates how to create the UI element in your PingFederate to configure your custom service URL and how the can you use the same service URL to authenticate the users.

I am using NetBeans IDE 8.0.2. However, you can use this same concept from Eclipse as well.

The source code for this project is available in the below GitHub repository.
https://github.com/ashishmgupta/pingfederate-idp-pcv-authenticate

Open Netbeans.
Go to File > New > Java Class Library
Click Next

image

Type a name for the project. I chose “pingfederate-idp-pcv-authenticate”

image

The created file view [Window > Files ] for the project will look like below

image

In order to use and compile the project with the PingFederate SDK, locate the pf-protocolengine.jar in the local pingefederate installation folder [\pingfederate\server\default\lib].

image

copy the  to the lib folder in the projects file view which will now look like below.

image

Go to the Project view of the project [Window > Projects]

 
image  

image

The reference to the pf-protocolengine.jar is now added.

image

Go to the File view and right click on the scr folder > Java Class >

image

Enter the class name as “pingfederate.passwordcredentialvalidators.Authenticator” and click Finished.

image

This created the below file

image

Extend the class from the PasswordCredentialsValidator. You do need to import few namespaces. Below is the complete class.

package pingfederate.passwordcredentialvalidators;

import com.pingidentity.sdk.GuiConfigDescriptor;

import com.pingidentity.sdk.PluginDescriptor;

import com.pingidentity.sdk.password.PasswordCredentialValidator;

import com.pingidentity.sdk.password.PasswordCredentialValidatorAuthnException;

import com.pingidentity.sdk.password.PasswordValidationException;

import java.util.Collections;

import org.sourceid.saml20.adapter.attribute.AttributeValue;

import org.sourceid.saml20.adapter.conf.Configuration;

import org.sourceid.saml20.adapter.gui.TextFieldDescriptor;

import org.sourceid.util.log.AttributeMap;

/**
 *
 * @author Ashish Gupta
 */
public class Authenticator implements PasswordCredentialValidator{
    
    private static final String authServiceURLLabel 
            = "Authentication service URL";
    private static final String authServiceURLDescription 
            = "The URL of the service which validates user's credentials";
    private static final String USERNAME = "username";
    private String authenticationURL = "";

    /*Creates a textfield for the authentication service URL in the Admin UI */
    @Override
    public PluginDescriptor getPluginDescriptor() {
       GuiConfigDescriptor guiDescriptor = new GuiConfigDescriptor();
       TextFieldDescriptor authServiceURLTextField = new TextFieldDescriptor
        (authServiceURLLabel, authServiceURLDescription);
       guiDescriptor.addField(authServiceURLTextField);
       PluginDescriptor pluginDescriptor = new PluginDescriptor
        (buildName(), this, guiDescriptor);
       /* Below will make the attributes available in the input Userid mapping 
       in the composite adapter If this is used inside the composite adapter.*/
       pluginDescriptor.setAttributeContractSet(Collections.singleton(USERNAME));
       return pluginDescriptor;
    }
   
    /* Get all the configured values in the PingFed admin e.g. Service URL */
    @Override
    public void configure(Configuration configuration) {
       this.authenticationURL = configuration.getFieldValue(authServiceURLLabel);
    }
    
    @Override
    public AttributeMap processPasswordCredential
        (String userName, String password) throws PasswordValidationException 
    {
       AttributeMap attributeMap = new AttributeMap();
       if(!AuthHelper.IsUserAuthenticated(this.authenticationURL, userName, password))
       {
           throw new PasswordCredentialValidatorAuthnException
        (false, "authn.srvr.msg.invalid.credentials");
       }
       else
       {
          /* The username value put here will be avilable to the next adapter 
           in the composite adapter chain*/
          attributeMap.put(USERNAME, new AttributeValue(userName));
       }
       return attributeMap;
    }
      
    private String buildName() {
        
        return "Custom password credential validator";
        /*
        Package plugin = Authenticator.class.getPackage();
        return plugin.getImplementationTitle();//+ " " + plugin.getImplementationVersion();         
        */
    }
}

Focus on the below methods in the /Authenticator.java:-

  1. getPluginDescriptor()
    This can be used to configure any set of UI elements which needs to be configured by the PingFed administrator. In this example, It creates a textfield for the authentication service URL in the Admin UI. This is where the PingFederate administrator would configure the service URL.

  2. configure(Configuration configuration)
    This can be used to get the configured values from the UI elements (set thie getPluginDescriptor) to the class level variables which then can be used in the processPasswordCredential() method [described below].

  3. processPasswordCredential(String userName, String password)
    Takes the username and password from the input fields in the HTML form and authenticates the user with your service. Ignore the implementation details of the service. If the authentication service does not allow the service, this method should throw the PasswordCredentialValidatorAuthnException with “false” and a string which shows up to the user in the HTML login form as an error message.

One more thing – You do have to identify this project as a password credential validator.
For this – add a folder named PF-INF under src and name it “password-credential-validators”.
Put the fully qualified name of the class, in this case – pingfederate.passwordcredentialvalidators.Authenticator.

image

Build the project and by default the jar file would be created under /dist folder. However, you can change the default location by changing the “dist.dir” property in the nbproject/project.properties file.

image

Now we have developed and deployed the custom PCV, Its time to configure the same in the
PingFederate admin console.

Configuring the custom PCV in PingFederate Admin console

Go to Server Configuration > Authentication > Password Credential Validators

image

Click “Create New Instance”

image

You can see the Type of the PCV in the type dropdown. Note that the text shown as the type here
is controlled by the name you provide in the below class instantiation in the getPluginDescriptor method as described above.

PluginDescriptor pluginDescriptor = new PluginDescriptor (“Custom Password Credential Validator”, this, guiDescriptor);

Provide a name and instance id as well and click Next.

image

Provide the service URL and click Next.

image

Notice the core contract attribute is username.
This is the attribute we set in the processPasswordCredential(). If the user is authenticated, we put the user name in this same attribute so that It is available for the next adapter in the composite adapter chain If this PCV is used in a composite adapter.

attributeMap.put(username, new AttributeValue(userName));

Click Next and Done and then

image

Below screen shows the summary. Click Done in this screen.

image

Click Save in the below screen.

image

You have successfully developed, deployed and configured a password credential validator in PingFederate.
In the forthcoming articles we will see how we can use this PCV in a adapter and set up the adapter for user authentication via HTML form.

, ,

Leave a comment

Minimizing SQL Injection – Dynamic SQL with IN Clause and QUOTENAME()

Use case

The application gets a list of comma separated account codes which need to be looked up in an Account table using the IN clause in a dynamic query. The application can not use prepared statements or stored procedures. The objective is to minimize the risk of SQL injection. In this post we will discuss potential SQL injection using dynamic SQL and using an SQL server inbuilt function named QUOTENAME() to minimize the risk of SQL injection.

Setting up Account table and sample data

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF OBJECT_ID('Account') IS NOT NULL
BEGIN 
DROP TABLE Account
END

GO

CREATE TABLE [dbo].[Account](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AccountCode] [varchar](50) NULL,
    [AccountName] [nvarchar](200) NULL,
 CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
)
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Account] ON
INSERT [dbo].[Account] ([Id], [AccountCode], [AccountName]) 
VALUES (1, N'XOD814', N'Test account1')
INSERT [dbo].[Account] ([Id], [AccountCode], [AccountName]) 
VALUES (2, N'GUR78437', N'Test account2')
INSERT [dbo].[Account] ([Id], [AccountCode], 
[AccountName]) VALUES (3, N'TIY5', N'Test account3')
INSERT [dbo].[Account] ([Id], [AccountCode], [AccountName]) 
VALUES (4, N'KOS370', N'Test account4')
SET IDENTITY_INSERT [dbo].[Account] OFF

After setup the Accounts looks like below :-

image

 

Input data  – ‘XOD814,GUR78437,ABCD’); DROP TABLE Account; –’
There are three items in the list which we want to look for in our table using the IN clause :-

  1. XOD814
  2. GUR78437
  3. ABCD’); DROP TABLE Account; –

As you can imagine, It’s the third item which might cause the table to be dropped. Let us see how :-

DECLARE @Query NVARCHAR(max)
SET @Query = ‘SELECT * FROM Account WHERE AccountCode IN (”XOD814”,”GUR78437”,
”ABCD”); DROP TABLE Account; –”)’
SELECT @Query AS ‘Executed query’
EXEC (@Query )
IF OBJECT_ID (‘Account’) IS NOT NULL
BEGIN
    SELECT ‘Account table exists’
END
ELSE
BEGIN
    SELECT ‘Account table got dropped.’
END
GO

If you zoom in to the highlighted statement above :-

image

A – The actual value ‘ABCD’ is terminated by a single quote (which is escaped here in order to make it a proper SQL statement) and then with a ending bracket and a semicolon making the end of the select statement. The subsequent drop statement become now a qualified correct statement to be executed.

B – The DROP TABLE statement is now terminated by a semicolon followed by double dashes commenting out any possible subsequent statements which might get added by dynamic SQL formation.

Executing the above Sql script would get the table stopped because of the below resulting query

Resulting SQL query
SELECT * FROM Account WHERE AccountCode IN (‘XOD814′,’GUR78437’,’ABCD‘); DROP TABLE Account; –‘)
NOTE:   '); closes the original statement and then a second statement (drop table) follows.

image

QUOTENAME() Function

QUOTENAME() function on a value delimits the value with a character (for example – a single quote) which can also be specified in the function. So, even if the value contains some other characters like quotes or semicolons to inject harmful SQL statements, the value is delimited and those harmful SQL statements will now become part of the string to be searched in the table rather than valid SQL statements to be executed and causing harm!

Below is the same query with all the values enclosed in QUOTENAME() with a single quote as delimiter. So, the client application can split the list of values and enclose each value in QUOTENAME() function before sending the dynamic SQL to SQL server.

DECLARE @Query NVARCHAR(max)
SET @Query = ‘SELECT * FROM Account WHERE AccountCode IN (‘+QUOTENAME(‘XOD814′,””)+’,’+QUOTENAME(‘GUR78437′,””)+’,’+QUOTENAME(‘ABCD”); DROP TABLE Account; –‘,””)+’)’
SELECT @Query AS ‘Executed query’
EXEC(@Query)
IF OBJECT_ID (‘Account’) IS NOT NULL
BEGIN
    SELECT ‘Account table exists’
END
ELSE
BEGIN
    SELECT ‘Account table got dropped.’
END
GO

Resulting SQL Query
SELECT * FROM Account WHERE AccountCode IN (‘XOD814′,’GUR78437’,’ABCD”); DROP TABLE Account; –‘)
NOTE – ”);
can not close the original statement because the single quote is escaped.

image

 

Difference when using QUOTENAME()

So – what is the difference? Its just a single quote – highlighted in red below. QUOTENAME() delimits the whole ‘ABCD); DROP TABLE Account; –’ with single quotes and in that process escapes the single quote right after ABCD which would have otherwise terminated the string to ABCD which would be then terminated by ); followed by execution of DROP TABLE.

Executed Query without QUOTENAME()
SELECT * FROM Account WHERE AccountCode IN (‘XOD814′,’GUR78437’,’ABCD‘); DROP TABLE Account; –‘)
NOTE:   '); closes the original statement and then a second statement (drop table) follows.


Executed Query with QUOTENAME()

SELECT * FROM Account WHERE AccountCode IN (‘XOD814′,’GUR78437’,’ABCD”); DROP TABLE Account; –‘)
NOTE – ”);
can not close the original statement because the single quote is escaped.

Leave a comment

TIBCO BusinessWorks 6.0 release announcement

Details here – http://www.tibco.com/company/news/releases/2014/press1337.jsp

AN upcoming webinar which you can register – http://forms2.tibco.com/webinar-amx-bw6-q314.html?utm_medium=pr&utm_term=bw6pagepr&utm_content=textlink&utm_campaign=bw6apr14

It will use Eclipse IDE – this means more seamless usage of TFS via the eclipse-TFS plug in. It is a relief as I am not a big fan of the TFS integration capabilities of TIBCO BW Designer at this point.

clip_image002

Leave a comment

TIBCO BusinessEvents 5.1 Studio– JMS Test connection failed

Just in case you are facing JMS Test connection failure issue even with the latest version of BusinessEvents (At this point I have version 5.1), follow the below steps :-

1) Copy the following jar files to the below location :-

jar files :-
jms.jar
tibjms.jar

Location where the JAR files need to be copied:-
%BE_HOME%/be/5.1/lib/ext/tpcl

Note – In my BE installation, I didn’t find the above jar files, I copied them from other TIBCO products installation folders.

2) Set the path in the studio.tra file :-
location of the Studio.tra file :-
%BE_HOME%\be\5.1\studio\eclipse\configuration\Studio.tra
Where the path needs to set:-

Look for the “studio.extended.classpath” in the studio.tra file and set the path for the jar files after the existing paths. Something like this (added line is highlighted in bold) :-

studio.extended.classpath=%BE_HOME%/hotfix/lib/ext/tpcl;%BE_HOME%/lib/ext/tpcl;%AS_HOME%/lib;
%HAWK_HOME%/lib
;%BE_HOME%/be/5.1/lib/ext/tpcl/jms.jar;%BE_HOME%/be/5.1/lib/ext/tpcl/tibjms.jar

,

2 Comments

Tibco BusinessWorks – BW-JDBC-100034 "Configuration Test Failed. Exception [com.microsoft.sqlserver.jdbc.SQLServerException] occurred. com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset

GuptaAshish.com

BW-JDBC-100034 “Configuration Test Failed. Exception [com.microsoft.sqlserver.jdbc.SQLServerException] occurred. com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset

<ns1:JDBCSQLException xmlns:ns1 = “http://schemas.tibco.com/bw/plugins/jdbc/5.0/jdbcExceptions“>
<msg>”JDBC error reported: (SQLState = 08S01) – com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset ClientConnectionId:396457ef-182e-4ddb-8b50-57541ce45b3b”</msg>
<msgCode>BW-JDBC-100014</msgCode>
<sqlState>08S01</sqlState>
<detailStr>com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset ClientConnectionId:396457ef-182e-4ddb-8b50-57541ce45b3b</detailStr>
</ns1:JDBCSQLException>

I saw this issue in a Tibco BusinessWorks application using a SQL JDBC Driver 4.0 to connect to a SQL server 2008 R2 server. However, I believe that this could happen with any application written in Java/.NET using JDBC driver 4.0 connecting to SQL server 2008 R2 server.

:- Check If at least Microsoft® SQL Server® 2008 R2 Service Pack 2 is installed on the SQL server. If not, install it. If SP2 or Higher (e.g. SP3) is already installed and you still see the same issue – problem is something else.

?
Connect to the server from the SQL Management Studio and execute the below query :-

SELECT @@VERSION.
You should see the SP2 as…

View original post 176 more words

, , , ,

Leave a comment

TIBCO Cloud Bus – At a glance and more….

In the below screenshot, look at the URL closely and you will notice the highlighted Amazon AWS.
Yes – this is the TIBCO Classic Administrator hosted on TIBCO Cloud Bus which leverages Amazon Web Services for Its cloud computing platform. If this catches your attention – read on…..

image

TIBCO Cloud Bus is a subscription-based Integration Platform-as-a-Service (iPaaS). What? Another “As-A-Service” buzzword?  Simply put – iPaaS allows integration between cloud to cloud applications and also between cloud to on-premises applications. Cloud to on-premises application integration is important  because financial services companies which need to follow many security and regulatory compliances might need to keep some applications in house on as well as want to keep other applications hosted on TIBCO Cloud bus with seamless integration between on premises and cloud apps.

Hosting the apps on cloud means you don’t need your IT infrastructure team to set up an environment for you to deploy your application. In TIBCO cloud bus, you can provision your own TIBCO environment in really really short time before you quickly deploy your applications.
TIBCO Cloud bus can increase/decrease the number of machines that sit behind the services. It knows what the load on the application is and then automatically increase/decrease the horsepower required for the application.

For the connectivity from the TIBCO Cloud Bus to on premise TIBCO infrastructure , TIBCO Cloud Bus has to connect through VPN gateway from the public cloud to company’s datacenter. This would involve setting up VPN tunnel at company’s end which may involve company’s IT as well.

At the time of writing this, I have two areas to be research in TIBCO Cloud Bus. Will edit this post when I have done some more research – hopefully with some answers.
1) Cost Effectiveness
How cost effective is TIBCO Cloud bus VS on-premises TIBCO setup over a longer period of time? Why an organization won’t continue to grow on in-control on-premises infrastructure to host TIBCO services rather than using TIBCO cloud bus If it is not saving costs over a period of time. Need some case studies and statistics.

2) Security
How TIBCO Cloud Bus addresses the security concerns when It comes to hosting all the apps on the cloud OR integration between on-premises applications and cloud applications? Need some case studies.
 
All said and done, IMHO, we must acknowledge that cloud computing NOT just a buzzword anymore now. Its for real and here to stay. So when TIBCO, one of the leaders in integration and middleware industry has something to offer in this space, we just need to keep an eye on it – if not necessarily adopting the offering immediately.

If you are already bored of reading, try TIBCO Cloud Bus yourself (30 days trial) – If you need help in that, I have some help below. I have steps-by-step instructions on how to set up a TIBCO Cloud Bus environment below. Good Luck and Happy Learning!

Setting up a TIBCO Cloud Bus environment

Note :- For below steps,use Mozilla Firefox for browsing the Cloud Bus If you can. – I have seen issues in IE/Chrome.

1) Go to cloudbus.tibco.com.

clip_image010

2) Enter your TIBCO Access Point credentials. If you are not registered there, register at tap.tibco.com and use the same login and password here.

clip_image012

3) If you have the TIBCO BW installed in your machine. You already have the development environment and you can skip this step. If you don’t, you can download the installers. Click on Develop > TIBCO Cloud Bus Designer (Windows 64-bit) [If you have 64-bit windows installed]

clip_image016 
The TIBCO Cloud Business Designer has following in it.

clip_image018

a) Extract the zip file.
b) Open the silent file, Install-Designer.silent, in a text editor.
c) Review the license files in the license folder. If you accept the licenses, change the <acceptLicense> entry key to true in the silent file.
d) Change the <installPackageDirectory> entry key to your extracted temporary folder: C:\temp\TIB_tcb-designer_1.0.0_win_x86_64
e) Optionally update the values for the <installationRoot> and <environmentName> keys.
f) Save the silent file.
g) Run the installer: Install-Designer.cmd
h) After the installation is complete, start TIBCO Designer from the Windows Startup menu: Choose All Programs > TIBCO > Cloud Environment > TIBCO Designer 5.8.

4) Click on the deploy button to see the highlighted “Skyway” button.

clip_image020

5) Click on “Cloud Bus Starter Template” link to provision the BW, EMS and admin stack.

clip_image022

6) See more information on the stack we are going to provision

clip_image024 clip_image026

7) Clicking on “Proceed with Provisioning” will ask you to complete the required fields.

clip_image028

8) Click on Edit

clip_image030

9) I put the below values and click on Apply Button. Then click on the “Proceed with Provisioning” button.

Fields

Values

Stack Name

AshishTibcoCloudBus

TIBCO Domain Name

TibcoAdminOnCloud

TIBCO Domain User

Admin

TIBCO Domain password

ashish@123

EMS user

Admin

EMS password

admin

clip_image031

10) Initializing stack

clip_image033

11) Click Start to initialize the stack

clip_image035

12) Stack provisioning started

clip_image037

13) Stack is running

image

14) If you click on the “Tibco Admin URL” link above, the Tibco Admin is opened in a separate window.
That’s right – this TIBCO admin is running on cloud.
image

15) That’s it – you can deploy any TIBCO App to TIBCO Cloud bus as just like you did on your on-premises infrastructure – Just like I did – shown in the below screenshot.
image

, ,

Leave a comment

Unable to deploy Tibco BW application after configuring Tibco BW Process Monitor – ERROR – [TRA-000000] StreamGobbler(ERROR) : data = java.lang.NoClassDefFoundError: com/tibco/processmonitor/client/run Caused by: java.lang.ClassNotFoundException: com.tibco.processmonitor.client.run

As a part of Tibco BusinessWorks Process monitor, one needs to add following properties to the bwengine.tra file in the Tibco BusinessWorks machine.

#BW ProcessMonitor properties
java.start.class=com.tibco.processmonitor.client.run
tibco.clientVar.nJAMS/logMode=complete
tibco.clientVar.nJAMS/DataProvider/JMS/useJNDI=true
tibco.clientVar.nJAMS/DataProvider/JMS/useQueues=true
tibco.clientVar.nJAMS/DataProvider/JMS/destination=bwpm
tibco.clientVar.nJAMS/DataProvider/JMS/enabled=true
tibco.clientVar.nJAMS/DataProvider/JMS/user=admin
tibco.clientVar.nJAMS/DataProvider/JMS/password=
tibco.clientVar.nJAMS/DataProvider/JMS/server=tibjmsnaming\://My_EMS_IPAddress\:7222
tibco.clientVar.nJAMS/DataProvider/JMS/contextFactory=com.tibco.tibjms.naming.TibjmsInitialContextFactory
tibco.clientVar.nJAMS/DataProvider/JMS/connectionFactory=QueueConnectionFactory
tibco.clientVar.nJAMS/DataProvider/EngineLog/enabled=false

Added the above and after that I couldn’t deploy any BW application in our Tibco Administrator. The tsm.log showed the below error each time I attempted to deploy an application.

[TRA-000000] StreamGobbler(ERROR) : data = java.lang.NoClassDefFoundError: com/tibco/processmonitor/client/run Caused by: java.lang.ClassNotFoundException: com.tibco.processmonitor.client.run [TRA-000000] StreamGobbler(ERROR) : data = java.lang.NoClassDefFoundError: com/tibco/processmonitor/client/run Caused by: java.lang.ClassNotFoundException: com.tibco.processmonitor.client.run at java.net.URLClassLoader$1.run(Unknown Source)  at ava.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source)

The first line of the list of properties we added to the bwengine.tra has following :-
java.start.class=com.tibco.processmonitor.client.run
Of course – It was
bwengine was not to find that class. Started looking into which jar would contain this file and It turns out to be bwpm.jar which comes as a part of BWPM install package.

image

Adding that bwpm.jar to C:\tibco\bw\5.10\lib resolved the issue and I could deploy BW apps to BW classic admin without any issues.

,

Leave a comment

Tibco BusinessWorks – BW-JDBC-100034 "Configuration Test Failed. Exception [com.microsoft.sqlserver.jdbc.SQLServerException] occurred. com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset

 

BW-JDBC-100034 “Configuration Test Failed. Exception [com.microsoft.sqlserver.jdbc.SQLServerException] occurred. com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset

<ns1:JDBCSQLException xmlns:ns1 = “http://schemas.tibco.com/bw/plugins/jdbc/5.0/jdbcExceptions“>
<msg>”JDBC error reported: (SQLState = 08S01) – com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset ClientConnectionId:396457ef-182e-4ddb-8b50-57541ce45b3b”</msg>
<msgCode>BW-JDBC-100014</msgCode>
<sqlState>08S01</sqlState>
<detailStr>com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset ClientConnectionId:396457ef-182e-4ddb-8b50-57541ce45b3b</detailStr>
</ns1:JDBCSQLException>

I saw this issue in a Tibco BusinessWorks application using a SQL JDBC Driver 4.0 to connect to a SQL server 2008 R2 server. However, I believe that this could happen with any application written in Java/.NET using JDBC driver 4.0 connecting to SQL server 2008 R2 server.

Solution :- Check If at least Microsoft® SQL Server® 2008 R2 Service Pack 2  is installed on the SQL server. If not, install it. If SP2 or Higher (e.g. SP3) is already installed and you still see the same issue – problem is something else.

How to check If at least SP2 is installed?
Connect to the server from the SQL Management Studio and execute the below query :-

SELECT @@VERSION.
You should see the SP2 as the part of result

image

Why :- See below :-
http://support.microsoft.com/kb/2653857
FIX: You cannot connect to SQL Server by using JDBC Driver for SQL Server after you upgrade to JRE 6 update 29 or a later version

What If you can not install SP2
We should always update latest service packs on the SQL server – not only for this issue. However, If you couldn’t and you had to live with current database server install – try using the below attribute in your TRA files.

java.property.TIBCO_SECURITY_VENDOR=j2se

If you are trying to test connection from the Tibco BusinessWorks Designer, add the above attribute in your <TIBCO_HOME>\designer\5.8\bin\designer.tra file.

If you want to deploy this change, add the above attribute in your <TIBCO_HOME>\domain\<Domain Name>\application\<Application>\<Application>.tra file and restart the service instances of the application.

NOTE:-
This same issue does not happen when you connect to Sql server 2005. That’s weird! Sounds like somebody broke something in Sql 2008 R2 and fixed it in SP2.


Additional information
Version number for SQL server in RTM and service packs.
image
[Source :http://sqlserverbuilds.blogspot.com/]

, ,

1 Comment

Sql Server job – Cannot insert the value NULL into column ‘owner_sid’, table ‘msdb.dbo.sysjobs’

I needed to script out a Sql job one Sql server (server1) and use the same script to create the same job on a different Sql server machine(server2). So, I scripted out the job from Server1 and ran the same on the Server2 and got the below error :-

Cannot insert the value NULL into column ‘owner_sid’, table ‘msdb.dbo.sysjobs’

Looking at more closely on the script, I noticed that the the script I created from the job on the Server1 also has the login which I used to login to Server1. This login is different from the one I was using to run the script on server2. See the highlighted login in the below screenshot.

image

Once I changed the script to put the login I am using to run the script on server2, the script ran fine and the job got created in the Server2.

image

, ,

1 Comment

Maximum connections exceeded!!!! – How to know who are logged on to a server and not letting you in?

We all hate to see this message when we want to login to the server :-
“The terminal server has exceeded the maximum number of allowed connections.”

image

All you need is the PsLoggedOn utility from sysInternals (now owned by Microsoft). Another reason we should all love Mark Russinovich and SysInternal tools.
Just follow the steps :-

  1. Download PSTools from here.
  2. Extract the zip file to a folder.
  3. Open command prompt and navigate to the extracted folder.
  4. Run the following command. Keep the \\ and replace the MachineName with the fully qualified name of the machine where you want to see who are currently logged on.

5.   You will see who are logged on to that machine.

psloggedon \\MachineName 

This saves you sending emails to a group to find out who are logged into the server. Rather – because you know who are logged in, you would ping/email those specific users, asking them to log off.

,

Leave a comment

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

Follow

Get every new post delivered to your Inbox.

Join 30 other followers