Technical: Apache / Tomcat – Microsoft SQL Server – Sample Database Query

Technical: Apache / Tomcat – Microsoft SQL Server – Sample Database Query

 

Introduction

As part of my attempt to work with my brother to dig a bit deeper into a Java based application running on Tomcat, I chose to write a simple HelloDB application in Java-JSP-JSTL.

 

Microsoft SQL Server JDBC Driver

Download Location

The latest Microsoft SQL Server JDBC Driver is v4.0.  And, the URL is  http://www.microsoft.com/en-us/download/details.aspx?id=11774

 

Extract files

We need a couple of Jar files from the compressed (*.tar.gz) file.  On Linux we will use tar to extract the needed jar files.

Syntax:

tar -zxvf <compressed-file>  *.jar

Sample:

tar -zxvf /tmp/sqljdbc_4.0.2206.100_enu.tar.gz *.jar

 Output:

extractJarFiles

 

Where is $CATALINA_HOME/lib

We installed Apache Tomcat version 6 using yum, and our install location is /usr/share/tomcat6.

Underneath /usr/share/tomcat there is a lib folder; which is actually a symbolic link to /usr/share/java/tomcat6.

 

usr-share-tomcat6 folder listing

 

 

Copy JDBC Jar files into $CATALINA_HOME/lib

We need to copy sqljdbc4.jar unto the lib folder.

Copy Files

Syntax:

sudo cp <jar-files> <tomcat>/lib

Sample:

sudo cp sqljdbc_4.0/enu/*.jar /usr/share/tomcat6/lib

 

 Output:

usr-share-tomcat6-lib-sql

 

JSTL

Download Location

Download javax.servlet.jsp.jstl-1.2.1.jar from http://search.maven.org/#browse|-1002239620

 

Code – WAR File

File Contents

Here are the regular files that we will be including in our Jar file:

  • WEB-INF/web.xml
  • WEB-INF/lib
  • WEB-INF/classes
  • META-INF/context.xml
  • html files
  • image files
  • jsp files
  • 3rd Party Jar files – jstl files

 

Here are the files that we will not be including:

  • Notice that you do not to indicate the manifest file, as that file is auto created

 

WAR File – Folder Structure

 

Folder Folder/Sub-file What? Sample
WEB-INF Web Information
web.xml Web Configuration File
 lib Jar files 3rd party and application jar files (jstl*.jar)
 classes Compiled java files
META-INF Meta Information about the Jar file
 context.xml

 

 

Source Code Location

On our system, we will place our source files in /home/dadeniji/development/apache.org/tomcat/MSAdventureWorksDWDB

 

 

Create empty file structure

Create empty file structure


if [[!-e WEB-INF ]]; then
   sudo mkdir -p WEB-INF


if [[!-e WEB-INF/lib ]]; then
   sudo mkdir -p WEB-INF/lib

if [[!-e WEB-INF/classes ]]; then
   sudo mkdir -p WEB-INF/classes


if [[!-e META-INF ]]; then
   sudo mkdir -p META-INF



 

 

WEB-INF/web.xml

Create WEB-INF/web.xml

Here is the contents of our web xml file:


<?xml version="1.0" encoding="ISO-8859-1"?>

<web-app
   version="2.5"
   xmlns="http://java.sun.com/xml/ns/javaee" 
   xsi="http://www.w3.org/2001/XMLSchema-instance" 
   schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

    <resource-ref>
        <description>DB  Connection</description>
        <res-ref-name>msadworks</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    	<res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>

</web-app>



 

There are a couple of areas to note:

  • Our app version is targeting Servlet/JSP API version 2.5
  • For our DB, we are defining a resource self-named msadworks and its type is javax.sql.DataSource

 

 

META-INF/context.xml

Create META-INF/context.xml

<?xml version="1.0" encoding="UTF-8"?>

<Context>

 <Resource
  name="msadworks"
  auth="Container"
  type="javax.sql.DataSource"
  username="ADWorksDW"
  password="sleeper"
  driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
  url="jdbc:sqlserver://hrdb;databaseName=AdventureWorksDW2008R2;"
  maxActive="1000"
  maxIdle="30"
  maxWait="10000"
  factory="org.apache.commons.dbcp.BasicDataSourceFactory" 
  logValidationErrors="true" 
 >
 </Resource>


</Context> 
 

There are a couple of areas to note:

  • We are further defining our database resource, msadworks
  • Its type is javax.sql.DataSource
  • The username & password that is predefined on the DB are noted
  • The Driver Class Name for MS SQL Server JDBC 4.0 is indicated as com.microsoft.sqlserver.jdbc.SQLServerDriver
  • The Url is “jdbc:sqlserver://<sqlmachinename>;databaseName=dbName
  • For the Connection factor we are hard-coding org.apache.commons.dbcp.BasicDataSourceFactory
  • And, logging validation errors via set setting logValidationErrors to true

 

Copy jstl jar files into WEB-INF lib

Here are the steps to copy the jstl files under WEB-INF/lib:

 


if [[ !-e WEB-INF/lib ]]; then
   sudo mkdir -p WEB-INF/lib

sudo cp --update /tmp/jstl/javax.servlet.jsp.jstl-1.2.1.jar  WEB-INF/lib

sudo cp --update /tmp/jstl/javax.servlet.jsp.jstl-api-1.2.1.jar  WEB-INF/lib

Source Code :- jspSimple.jsp

Here is a sample source code that connects to the hard coded sql server and relies on JDBC APIs to query and return the recordset from a table.

<html>
<head>jspSimple.jsp</head>
<body>
 <%@ page import="java.sql.*" %>
 <% 
 String connectionUrl = "jdbc:sqlserver://hrdb:1433;databasename=AdventureWorksDW2008R2;user=AdWorksDW;password=sleeper";

 String strSQLStatement =
 "select top 3 FirstName, LastName, EmailAddress from dbo.DimCustomer";
 
 Connection con = null;
 Statement statement = null;
 ResultSet resultset = null;
 
 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
 con = DriverManager.getConnection(connectionUrl); 
 statement = con.createStatement() ; 
 resultset = statement.executeQuery(strSQLStatement);
 %>
 
 <table> 
 <tr>
 <th><b>First Name</b></th>
 <th><b>Last Name </b></th>
 <td><b>Email Address</b> </th>
 </tr>

<% 
 while (resultset.next())
 { 
%>
 
<tr> 

 <td><%= resultset.getString("FirstName") %></td> 
 <td><%= resultset.getString("LastName") %></td> 
 <td><%= resultset.getString("EmailAddress") %></td>

</tr>

<% 
} 
%> 

</table>
</body> 
</html> 

 

 

Source Code :- explicit.jsp

The code below relies on the Context (part of javax.naming.*) and DataSource (part of javax.sql*).

Obviously, javax.naming.Context allows us to hide the server name and authentication credentials in xml files outside of the Source Code.

<%@ page contentType="text/html;charset=UTF-8" %> 
<%@ page import="java.sql.*" %> 
<%@ page import="javax.sql.*" %> 
<%@ page import="javax.naming.*" %> 
<HTML>
<HEAD>
<TITLE>JSP example</TITLE>
</HEAD>
<BODY>
 <h1>Hello,test JNDI ! </h1>
 <%
 Context ctx = new InitialContext(); 

 Context envctx = (Context) ctx.lookup("java:comp/env"); 
 DataSource ds = (DataSource) envctx.lookup("msadworks"); 

 //DataSource ds = (DataSource) ctx.lookup("msadworks");

 Connection conn=ds.getConnection(); 
 Statement st=conn.createStatement();
 String sql="select top 3 FirstName, LastName, EmailAddress"
             + " from dbo.DimCustomer"; 
 ResultSet rs=st.executeQuery(sql); 

 while(rs.next()) 
{

 %> 
    FirstName:<%=rs.getString(1) %> 
    LastName:<%=rs.getString(2) %> 
    EmailAddress:<%=rs.getString(3) %> 

  <br>

 <%
 }
 %> 

 <%
 rs.close(); 
 st.close(); 
 conn.close(); 
 %> 
</BODY>
</HTML> 



 

 


Source Code :- queryDB.jsp

The code below uses JSTL tags and allows us closer to HTML/Code Fusion type programming.

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" 
    prefix="c" %>

<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" 
    prefix="fmt" %>

<%@ taglib uri="http://java.sun.com/jsp/jstl/functions"
    prefix="fn" %>

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" 
    prefix="sql" %>


<sql:query var="rs"
    dataSource="msadworks"
>
   select top 3 FirstName, LastName, EmailAddress 
   from dbo.DimCustomer

</sql:query>



 <head>
 <title>DB Test</title>
 </head>

 <body>

 <h2>Results</h2>


 <table border="1" align="center" valign="center">
 <th>Id</th>
 <th>Name</th>
 <th>Address</th>
 <c:forEach var="rs" items="${rs.rows}">
 <tr>
 <td><c:out value="${rs.FirstName}"/>&nbsp;</td>
 <td><c:out value="${rs.LastName}"/>&nbsp;</td>
 <td><c:out value="${rs.EmailAddress}"/>&nbsp;</td>
 </tr>
 </c:forEach>
 </table>
 

 </body>

</html>




 

 

 

Create Jar file

Here are the steps to create a jar file

Syntax:

jar cvf <war-file> *.jsp *.html images WEB-INF META-INF
 

 

Sample:

jar cvf MSAdventureWorksDWDB.war *.jsp *.html images WEB-INF META-INF
 

 

View Jar file Contents

View Jar file contents

Syntax:

jar tf <jar-file>
 

Sample:


jar tf MSAdventureWorksDWDB.war

Output:

jar file listing

 

 

 

Deploy – Manual

Manually deploy jar file by copying the war file into your webapps folder

There is a very noteworthy coverage of manually deploying WAR file available @

http://www.manydesigns.com/en/portofino/portofino3/3_1_x/installation-guide/deploying-on-tomcat/#TOC-Manually-deploying-and-configuring

 


#un-deploy war
if [[ -f "/usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war" ]]; then
 echo "Removing file /usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war ..."
 sudo rm "/usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war"
 echo "Removed file /usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war"
fi


#deploy war
echo "Deploying file /usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war ..."

 sudo cp MSAdventureWorksDWDB.war /usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war

echo "Deployed file /usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war"

 

 

Deploy – Verification – CATALINA_HOME/conf/Catalina/localhost/<APP-NAME>.XML

 

File Name:

Review CATALINA_HOME/conf/Catalina/localhost and look for a configuration file (XML) bearing your Application Name.

In our case, our folder’s name is /usr/share/tomcat/conf/Catalina/localhost

folder usr-share-tomcat6-conf-catalina-localhost

And, the file we are looking for is MSAdventureWorksDWDB.xml

 

File Contents:

catalina--localhost--app-config--contents

 

 

 

TroubleShooting

TroubleShooting – Log files

As Apache Tomcat is a server side architecture one needs to pay attention to its log files to see how things are going.

On Apache Tomcat 6, the log files are placed in /var/log/tomcat6

 

folder-list--var-log-tomcat6

 

 

 

Diagnostic Steps

Use lsof

Use lsof to determine where sqljdbc4.jar is being loaded from:


[dadeniji@adelia MSAdventureWorksDWDB]$ sudo lsof | grep sqljdbc
java 13196 tomcat mem REG 253,0 584207 522998 /usr/share/java/tomcat6/sqljdbc4.jar
java 13196 tomcat 25r REG 253,0 584207 522998 /usr/share/java/tomcat6/sqljdbc4.jar
[dadeniji@adelia MSAdventureWorksDWDB]$


 

Stolen Code from dinesh

Here is a piece of code stolen from Iscocra Consulting @ http://isocra.com/2007/10/jndi-problems-with-tomcat-5515/




<%@ page contentType="text/html;charset=UTF-8" %> 
<%@ page import="java.sql.*" %> 
<%@ page import="javax.sql.*" %> 
<%@ page import="javax.naming.*" %> 

<%

 Context initContext = new InitialContext();
 out.println("1. Got the initial context"+ "\n <br/>");
 String envContextName = "java:/comp/env";
 Context envContext = (Context)initContext.lookup(envContextName);
 out.println("2. Got the context: "+ envContextName + "\n <br/>");
 String jndiName = "msadworks";
 DataSource ds = (DataSource)envContext.lookup(jndiName);
 out.println("3. Got the datasource: "+ds.getClass().getName()
                  +" for context "+jndiName + "\n <br/>");
 Connection conn = ds.getConnection();
 out.println("4. Got the connection: "+conn);


%>


 

Run-Time Error Messages

Wrong Folder Group

SEVERE: Error starting static Resources
java.lang.IllegalArgumentException: Document base /webapps/MSAdventureWorksDWDB does not exist or is not a readable directory
at org.apache.naming.resources.FileDirContext.setDocBase(FileDirContext.java:142)

 

webapp_wrong_group_root_shouldBe_tomcat

References - Name is not bound in this context




18:     //Context envctx =  (Context) ctx.lookup("java:comp/env"); 
19:     //DataSource ds =  (DataSource) envctx.lookup("jdbc/MSAdventureWorksDWDB");  
20: 
21:     DataSource ds = (DataSource) ctx.lookup("jdbc/msadworks");
22: 
23:     Connection  conn=ds.getConnection();   
24:     Statement  st=conn.createStatement();


Stacktrace:
root cause

   javax.servlet.ServletException: javax.naming.NameNotFoundException: 
   Name jdbc is not bound in this Context


Does not work:

//Context envctx = (Context) ctx.lookup("java:comp/env");
//DataSource ds = (DataSource) envctx.lookup("jdbc/msadworks");

DataSource ds = (DataSource) ctx.lookup("msadworks");

 
Works:


Context envctx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource) envctx.lookup("jdbc/msadworks");

//DataSource ds = (DataSource) ctx.lookup("msadworks");


 

 

 

Actual SQL Database Connectivity Error – SQL Instance Connectivity



Stacktrace:
	org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:510)

javax.servlet.ServletException: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'sa'. 

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'sa'. 

note The full stack trace of the root cause is available in the Apache Tomcat/6.0.24 logs.


Actual SQL Database Connectivity Error – SQL Instance \ Database Connectivity


Stacktrace:
	org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:510)
	org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:401)
	org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
	org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "AdventureWorkDWs2008R2" requested by the login.

note The full stack trace of the root cause is available in the Apache Tomcat/6.0.24 logs.


 

 

Actual SQL Database Connectivity Error – SQL Instance Connectivity – Wrong User/Password


javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Login failed for user 'AdventureWorksDW_v2'. ClientConnectionId:cd487d05-422d-4dcd-9cdd-f26d236428dd)"

javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Login failed for user 'AdventureWorksDW_v2'.

In our case, we had the wrong password

 

Actual SQL Database Connectivity Error – SQL Instance \ Database Connectivity

In the /var/log/tomcat6/catalina.<YYYY-MM-DD>.log, you might see warnings about a missing dll sqljdbc_auth.dll.

The warning speaks about the aforementioned file missing from the java.library.path.


Apr 24, 2014 11:13:15 AM com.microsoft.sqlserver.jdbc.AuthenticationJNI 
WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in 
java.library.path
Apr 24, 2014 11:13:15 AM org.apache.coyote.http11.Http11Protocol destroy

If your environment/OS is not Microsoft Windows, then you are OK.

It simply means you will not be able to use trusted authentication.

 

Actual SQL Database Connectivity Error - Cannot create JDBC Driver of class ” for connect URL ‘null’

 



org.apache.jasper.JasperException: javax.servlet.ServletException: 
javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'"

javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'"

javax.servlet.jsp.JspException: Unable to get connection, DataSource 
invalid: "org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC 
driver of class '' for connect URL 'null'"


 

The fix here could be so many things.  Here are some things to try out.

  • If you are manually modifying  files in your your WAR directory, you might run into trouble with proper creation\update of your xml config file that is housed in /usr/share/tomcat/conf/Catalina/localhost.When an application is properly deployed, the system does an excellent job maintaining this file.

Conclusion

I stumbled quite a bit with this simple Application.  And really, because I was a bit stuck with an unhelpful error message “Cannot create JDBC driver of class ” for connect URL ‘null’””, I dug and tried my best to have a clean room environment where I could really say this is the most straightforward way to fix that error.

But, it wasn’t to be.  And, so though my little app is working, I do not have comprehensive data to share as to what the best path to remedify is.

And, so you get to have Joe Nichols as my Listening song!

 

Listening

Listening to Joe Nichols – I wish that wasn’t all

http://mp3lemon.org/song/74220/Joe_Nichols_-_04_-_I_Wish_That_Wasnt_All

References

References – JSTL – Blog

 

References – JSTL – Jar Files Repository

 

References – JSTL – QA

 

References - Java Development – Jar

References - Java Development – War

 

References - Java Development – Directory Structure

References - Java Development – Naming Convention

References - Apache Tomcat – Installation

References - Apache Tomcat Installation on RedHat, CentOS, and Clones

 

References - Apache Tomcat - Deploy

 

References - Apache Tomcat - Deployment on MS Windows 2008/IIS v7

 

References - JDBC

References - JDBC – Connection Pooling – Concurrency

References - JDBC – Connection Pooling -

 

References - JDBC – Connection Pooling - Standalone

 

References - SQL Jar File Location

 

References - Sample Code

 

References - Sample Code – JSTL

 

References – QA – sql:setDataSource

 

 

References - Tomcat - Data Sources

 

 

References - Tomcat - Data Sources – External Context file

References - Tomcat - Data Sources – Q/A

 

 

References - Tomcat – JNDI Resources – Q/A

 

References - Vendor – jasig.org – uPortal 4.0

 

References - Error – Error Message – “WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path”

 

References - Error – Error Message – “Programmer’s Town » Java » tomcat 6 and No suitable driver found”

 

References – Error – “Ubuntu Tomcat7 java.lang.ClassNotFoundException: org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory”

 

The critical element is the “factory” declaration, which overrides the built-in default.On our production machines, the resource is defined in the GlobalNamingResources element of the server.xml file. Specifying the factory is only needed on the Ubuntu systems.

Sameeh Harfoush
I had the same problem on CentOS. I got around this by downloading a fresh copy of tomcat from site and uploaded tomcat-dbcp.jar to my online server lib, restart server :)

References – Problems – JNDI Lookup problem

 

References – Linux – Command – File Management – Mkdir

References – Linux – Command – File Management – Folder Copy

 

Posted in Apache, Technical, Tomcat | Leave a comment

Life & Its Songs – Paul Simon – “The Cool, Cool River”

Life & Its Songs – Paul Simon – “The Cool, Cool River”

Had a bit of down time yesterday and has I drove up 880 North, listened in on NPR.

This is the parting song on another wise tough subject area:

Paul Simon – Cool, Cool River
http://www.youtube.com/watch?v=7LUyp34g3p8

 

Paul playing with lyrics:

http://www.releaselyrics.com/0f0d/paul-simon-the-cool%2c-cool-river/

To prayers of devotion
And these prayers are
The constant road across the wilderness
These prayers are
These prayers are the memory of God
The memory of God

And I believe in the future
We shall suffer no more
Maybe not in my lifetime
But in yours I feel sure

 

Posted in Inspirational, LIfe | Leave a comment

Technical: Microsoft – IIS – Error – The current identity does not have write access to ‘C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files’.

Technical: Microsoft – IIS – Error – The current identity <identity-account> does not have write access to ‘C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files’.

Background

Ending up with errors running a Microsoft .Net Web Application.  The error message states that I do not have write permissions to the Temporary ASP.Net Files folder.

 

NTFS Permissions

 

use Calcs, get permissions on Temporary ASP.NET Files

Using calcs, list permissions on folder *\v2.0.50727\Temporary ASP.NET Files


CACLS "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files"

Got a lot of data, the most valuable are the entries for IIS_WPG:


CACLS "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files" | find /I "IIS_WPG"

Output:

calcs-IIS_WPG

 

It quickly dawned on me that I am looking at this the wrong way. Rather than attempt to explicitly grant permission to my Application Pool account, I should just add my Application Pool account to the machine’s IIS_WPG group.

 

Local Group — IIS_WPG

List local group users

net localgroup IIS_WPG

 

Output:

netlocalgroup--IIS_WPG

 

Add Application Pool Account to Local IIS_WPG group

Let us add our IIS Application Poo account to the machine’s IIS_WPG Group.

Syntax:

net localgroup IIS_WPG /add <Account>

Command:


net localgroup IIS_WPG /add "LAD-DOMAIN\AppPool"
The command completed successfully.

Output:

netlocalgroup--IIS_WPG--addAccount

Clear Temporary Folder

But, still ending up with errors until I cleared the Temp Folder.

Clear temp folder.

Syntax:



del "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files" /s /Q

del "C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files" /s /Q

FOR /D %G in ("C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\*") do RMDIR /S /Q "%G" 

FOR /D %G in ("C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files\*") do RMDIR /S /Q "%G"

 

References

Changing Temporary ASP.Net Files

Dos Commands

Dos Commands – rmdir

 

 

Posted in .Net, Internet Information Server (IIS), Microsoft, Technical | Tagged | Leave a comment

Technical: Microsoft – Network – Error – NetBt – A duplicate name has been detected on the TCP network

Technical: Microsoft – Network – Error – NetBt - A duplicate name has been detected on the TCP network

 

Background

Seeing a lot of errors bearing the signature “A duplicate name has been detected on the TCP network. The IP address of the machine that sent the message is in the data. Use nbtstat -n in a command window to see which name is in the Conflict state.”

 

Event Viewer

For System Logs, turned on Filtering for Event Source = NetBT

System-FilteredOn-Source--NetBT

 

And, here is a run down of errors

EventViwer-System-FilteredOnNetBT

 

Diagnosis

Thanks Microsoft for good directions, the error message is precinct as it clearly reads:

Use nbtstat -n in a command window to see which name is in the Conflict state.

 

And, so started a command shell and issued “nbtstat -n” and here is what we came back with.

nbtstat -n -- before

 

We readily see that we have two sets of data that have identical Netbios name;   there are two IP Addresses.

Upon looking more closely the two IP Addresses are on the same box, as it is multi-home box.

 

Resolution

To fix please access network connections and disable “Netbios over TCP/IP” on the public/internet facing Interface.

Here are the steps:

  • Launch Control Panel
  • Access the Network applet
  • Select the Interface we want to review
  • In the General Tab, select “Internet Protocol (TCP/IP) properties”
  • Access the “General” Tab and click on the “Advanced” button
  • In the “Advanced TCP/IP” settings, access the “WINS” tab
  • In the “Netbios setting” panel, select the “Disable Netbios over TCP/IP” button

 

DisableNetbioisOverTCPIP

 

 

Review Results

Upon re-issuing “nbtstat -n”, we see a single result set:

nbtstat -n -- after (2)

 

One needs to pay attention as we can see that we have kept the two interface connections.  It is just that the Internet Interface is empty.

It is no longer registering any Netbios names and the entries are replaced with “No names in cache“.

Later review of our event log shows that we do not have recent error entries.

 

Summary

I was not so sure what the acronym “NetBt” stood for.  And, so goggled for it, and I am glad I did.  Wikipedia contains good data @ NetBIOS over TCP/IP (http://en.wikipedia.org/wiki/NetBIOS_over_TCP/IP).

In the “Security Vulnerability” section of the article, one is strongly encouraged to disable the services:

Two such vulnerable network protocols that provide services are: the Server Message Block (SMB) protocol and NetBIOS over TCP/IP. Both services can reveal incredible amounts of detail and vital, security information about an exposed network. When not mitigated, NetBIOS over TCP/IP and SMB provide recurring vectors for malicious attacks upon a network. Specifically, NetBIOS provides attackers with a means to map the network and also freely navigate a compromised intranet. In regards to public Web Servers, neither service is necessary for the successful operation of a public Web server and disabling both services in such scenarios can greatly enhance the security status of a network.

 

Posted in Microsoft, NetBIOS over TCP/IP (NetBt), Networking, Technical | Tagged , , , , | Leave a comment

Technical: Microsoft – SQL Server – Backup Failing with error Operating System error 5 (Access is denied)

Technical: Microsoft – SQL Server – Backup Failing with error Operating System error 5 (Access is denied)

Background

Checking a SQL Server backup and seeing that backups are failing.

 

Error

Error Message:



Error: 18204, Severity: 16, State: 1.

BackupDiskFile::CreateMedia: Backup device '\\backupServer\DBbackupProduction\model_backup_2014_04_17_094400_0249947.trn' failed to create. Operating system error 5(Access is denied.).

Error: 3041, Severity: 16, State: 1.



Error Image:
backupFailureErrorMessages

 

 

Security Audit - Backup Server – NTFS Share and Folder Permissions

The first thing I did was connect to the backup server using Computer Management.  Then accessed shared resources permissions set.

Computer Management – System Tools – Shared Folders – (choose shared resource) – Tab – Shared Permissions

ComputerManagement-SharedFolders-SharePermissions

 

Our service account has full permission to the Shared resource.

 

Computer Management – System Tools – Shared Folders – (choose shared resource) – Security Tab

ComputerManagement-SharedFolders-Security

 

Our service account has full permission to the NTFS folder.

 

Security Audit – SQL Server Agent - Log On As

I checked the SQL Server Agent over and over again and tried seeing why it will be failing.

runas

Also, investigated using runas… This will allow me to start a new session that fully impersonates our SQL Server Agent account.


C:\>runas /user:LABDOMAIN\SqlAccount  "dir \\dbBackupServer\sqlbackup"
Enter the password for LABDOMAIN\SqlAccount:
Attempting to start dir dbBackupServer\sqlbackup as user 
"LABDOMAIN\SqlAccount" ...
RUNAS ERROR: Unable to run - dir \\dbBackupServer\sqlbackup
1385: Logon failure: the user has not been granted the requested logon 
type at this computer.

 

Obviously, the “run as” failed as the account does not have Local Security Privileges “Logon locally”

The user has not been granted the requested

 

The “Logon Type” is important.  It is #2; which means “Interactive”.

But, even granting that account principal access on DB computer did not help.

 

Security Audit – SQL Server Service \ Log On As

I went back and checked the SQL Server DB itself and noticed that “SQL Server” is running as “LocalSystem”.

SQLServerConfigurationManager

I knew immediately that LocalSystem will usually not have network privileges.

And, so went ahead and changed SQL Server “Log on As” account.

Please do so using “Sql Server Configuration Manager” as doing so will allow the SQL Server Software to properly change to the new Account and make all necessary NTFS Security changes.

 

LogOnAsAccountChange

Please choose to restart your DB Engine.

Continue Reading

Logon Types Code

Please read through Randall F. Smith “Logon Type Codes Revealed” article for the best coverage of Logon Types ( http://www.windowsecurity.com/articles-tutorials/misc_network_security/Logon-Types.html )

 

Null Session Shares

If temporarily you will like to be continue to use Local System account on the DB Server and be able to access network shares on remote servers, please consider reading “How to enable null session shares on a Windows 2000-based computer”
http://support.microsoft.com/kb/289655 ).

Basically, it covers how to create a to a multi-string entry called HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares on the remote backup machine and adding each share you will like accessible by remote local system accounts.

 

Conclusion

I really had a rough time with this as I was looking to SQL Server Agent as being the instigator of backup activities.  But, really it ended up being the SQL Server Engine itself.

Please keep an eye on the “SQL Server VSS Writer” service, as well.

 

Posted in Backup, Microsoft, Technical | Tagged , , , | Leave a comment

Technical: Microsoft – Information Integration Server (IIS) – Version 8 – Hardening\Securing

Technical: Microsoft – Information Integration Server (IIS) – Version 8 – Hardening\Securing

 

Background

Still morose with the IIS web site hijack that I covered in a previous post (http://danieladeniji.wordpress.com/2014/04/12/technical-microsoft-dnsiis-attack-2014-04-12/).

Starting to take baby steps to harden IIS a bit.

 

Areas we will cover:

  • Web Server Extensions
  • Host Headers
  • Application Pools
  • Request Filtering – HTTP Verbs
  • Audit

 

Web Server Extensions

On the web server, here are the steps to review and adjust “ISAPI and CGI Restrictions”:

  • Launch IIS Manager
  • Select the Server
  • On the server level, make sure that you are viewing the “Features View”
  • Select the “ISAPI and CGI Restrictions” applet

Here is what our screen looks like:

ISAPIandCGIRestrictions

 

 

Interpretation

As we are not familiar with the hcap\hcapext.dll module, not so sure what to make of it, and so we changed his Restriction from “Allowed” to “Not Allowed”.

 

 

Host Headers

On the web server, here are the steps to effect Host Header

  • Launch IIS Manager
  • Select the Server
  • Access the web site
  • At the web site level, transverse to the “Access” panel
  • In the “Edit Site” branch, click the “Bindings” branch
  • Review listed bindings and make sure that you have specific URLs listed; including the one for localhost, if you will be browsing from the local machine

 


SiteBindings-AddSiteBinding

 

This helps shield you from fly by night visitors; i.e those that are just performing IP Sweeps.

 

 

Application Pools

Review registered Application Pools and make sure that you ‘re using Active Directory accounts or local accounts with the most basic permission set.

On the web server, here are the steps to effect Host Header

  • Launch IIS Manager
  • Select the Server
  • Access the “Application Pools”
  • Review each Application pool, service account
  • Select the Application Pool, right click on your selection, and from the drop-down menu, choose “Advanced Settings” entry
  • In the “Advanced Settings” window, navigate to the Identity entry and note the Account that you ‘re running under

 

ApplicationPool-AdvancedSettings-Identity

 

Please review the Application Pool user account on your Web Server, Active Directory, and Database, etc.

And, later be able to audit the account in terms of it activities.

 

Application – Request Filtering – HTTP Verbs

Aforementioned, we are quite concerned that some of our web site files are getting over-written.

To address, we will enable “Request Filtering” and only allow the following verbs – Get, Post.

To effect, we will slightly modify the Application’s web.config file:

 

Here is the snippet of code that filters out unlisted verbs; and adds\enables the two verbs that we want to allow – GET and POST.

<configuration>

	 <system.webServer>
	  <security>
	   <requestFiltering>
		<verbs
		   allowUnlisted="false"
		   >
		 <add verb="GET" allowed="true" />
		 <add verb="POST" allowed="true" />		 
		</verbs>
	   </requestFiltering>
	  </security>
	 </system.webServer>
	   
</configuration>


 

To view the settings:

  • Launch IIS Manager
  • Access the web site
  • Ensure that the “Features Views” is active
  • Within the “IIS” sub-section, choose “Request Filtering”
  • In the “Request Filtering” window, select “HTTP Verbs” tab

  Request Filtering:

RequestFiltering--HTTPVerbs

 

 

Audit

Audit – NTFS

Our primary hope at this time is to be better prepared to audit NTFS changes that is shamefully causing our website contents to be over-written.

Thankfully, Microsoft has built NTFS to be capably audit-able.

Steps:

There are two steps.  The first one is to select the object we will like to audit and specify which actions we will like to audit.

Steps – Specify actions to audit

  • Launch Windows Explorer
  • Access Folder that contains web site folders & files
  • Right click on your selection, and from the drop-down menu, choose “Properties” entry
  • In the “Properties” window, access the security tab, and click on the “Advanced” button
  • In the “Advanced Security Settings” window, access the “Auditing” tab window
  • Review the accounts that are currently being audited
  • In our case, we will be adding “Domain  Users” to our list
  • And, as we are not interested in execute nor read activities, but changes to the actual file, please click on “Show advanced permissions” button

Here are entries that we selected:

NTFS-Permissions-AuditingFolder

 

And, here is the completed list:

NTFS-Permissions-AuditingFolder-AdvancedSettings

 

 

Steps – Specify actions to audit

  • Launch Administrative Tools \ Local Security Settings
  • In the left panel, access Security Settings \ Local Policies\ Audit Policy
  • In the right panel, select “Audit object access” and double-click on your selection
  • In the “Audit object access properties” window, select “Success” and “Failure” — In many cases, it is OK to just audit failures — that is you want to know who is try gain access, but failure.  But, as I said, in our case, whomever or whoever is trying to gain access is successfully doing so
  • Click “OK” to enforce your changes

 

 

AuditPolicy-AuditObjectAccess-SuccessAndFailure

 

Completed:

Please pay close attention to the “Security Setting” column, as in many shops changes can not be effected, due to “Group Policy” settings.

LocalSecurityPolicy-AuditPolicy-Completed

 

 

Program Features

Review Program Features and see whether new applications have been installed.  In our case, we wanted to source hcapexet.dll.

Especially, as we have a file creation date.

ProgramAndFeatures

 

Good on Microsoft for augmenting “Add\Remove Programs” with an order-able “Installed On” column.   This way, we can correlate our initial problem date with Application Install days.

 

Conclusion

Who gets picked on ?  The security breach might very well be random in nature.  I know we sometimes feel picked on.  But, in the world wide web, there are no strangers and little anonymity.  We are all just IP friends and neighbors.

It seems the virus slash worm is targeting familiar folders; folders with names such as CascadingSyleSheets, Confide, DynamicData, and Northwind.

And, files with names such as default.asp and index.php.

Pasted below is a screen shot that shows infected folders and files.

 

familarFolders

The infected folders are:

  • App_Data
  • bin
  • Content
  • Controllers
  • Models
  • obj
  • Properties
  • Scripts
  • Views

And, the files are:

  • default.asp
  • index.php

The infection occurred on 4/7/2014 and the folders and files bear that DateModified timestamp .  Please keep in mind that even when we replace infected files with good ones, they are getting re-infected and the DateModified have the more recent dates.

 

 

References

References – General

References – Web Server Extensions

References – Web Server Extensions – IIS7

 

References - Host Headers

 

References - Request Filtering

 

Posted in Internet Information Server (IIS), Microsoft, Security, Technical | Tagged , | Leave a comment