CODEWITHSUNDEEP
sql-servermule
Tad
Tad

Reputation: 517

Mule connecting to SQL Server 2012

I cannot figure out to connect to a local instance of SQL Server 2012, either LocalDB or SQLExpress. I have Microsoft's sqljdbc4.jar driver in the path. I'm using Mule 3.3.1 CE; I don't have EE available to me yet.

I tried using LocalDB with no success. I found some posts that indicate that LocalDB might not be compatible with MS's driver and recommend a switch to SQLExpress. However, still no luck with that.

I am able to connect to my SQLExpress instance using SQLCMD (e.g. "sqlcmd -S DEFTA-1\SQLEXPRESS") and with SSMS using the credentials in my flow.

My configuration of the data source and connector is as follows:

<spring:beans>
    <spring:bean id="SQLServer_DataSource" name="Bean" class="org.enhydra.jdbc.standard.StandardDataSource">
        <spring:property name="driverName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <spring:property name="url" value="jdbc:sqlserver://DEFTA-1\SQLEXPRESS:1433;user=test_user;password=test_pwd;"/>
    </spring:bean>
</spring:beans>

<jdbc:connector name="Database" dataSource-ref="SQLServer_DataSource" 
    validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database">
    <jdbc:query key="InsertRecord" value="insert into eai.dbdusage (id,name) values (1,'hello world')"/>
</jdbc:connector>

I've tried changing the server specification use a double backslash in the server name, to leave off the port number, to use &#92; instead of the backslash...none of it works. I also tried something like "jdbc:sqlserver://localhost:1433;instanceName=DEFTA-1\SQLEXPRESS..." and "jdbc:sqlserver://localhost:1433;instanceName=SQLEXPRESS..."

The exception is:

Cannot get connection for URL jdbc:sqlserver://DEFTA-1\SQLEXPRESS:1433;user=test_user;password=test_pwd; : The TCP/IP connection to the host DEFTA-1, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

Is there something I need to do to enable a connection on the SQL Server side, or some change I need to make to my configuration?

Upvotes: 0

Views: 3203

Answers (4)

Dileep Dondapati
Dileep Dondapati

Reputation: 41

I got same issue, removed the PORT and now i am able to make connection

Try this:

jdbc:sqlserver://${mssql.server};databaseName=${mssql.database};user=${mssql.user};password=${mssql.password}

Upvotes: 0

Kent Weare
Kent Weare

Reputation: 11

This post really helped me out: http://java.dzone.com/articles/six-possible-solutions-jdbc-%E2%80%93

more specifically Point #3

3.If you still facing the issue then please check Registry ◦Go to Start Menu => run => regedit ◦Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.[Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp\IPAll and check the value of key TcpDynamicPorts. we have to consider that port number instead of default port 1433

In your connection use the value that is here instead of 1433. For me it was 26625

Upvotes: 1

Tad
Tad

Reputation: 517

I went into netstat and noticed that sqlserver.exe was listening on port 63304. Changing to that port number in the datasource string allows me to connect.

I'm puzzled by this as I thought SQL Server was always on port 1433 by default (clean install) but this works.

Upvotes: 0

Seba
Seba

Reputation: 2319

Have you enabled TCP/IP access on SQL Server? http://msdn.microsoft.com/en-us/library/hh231672.aspx

Upvotes: 0

Related Questions