MS SQL Server connection problem

One Star

MS SQL Server connection problem

I'm trying to set up a connection to a SQL Server database and I get an error saying that it is impossible to make a connection. I've tried to attach a screenshot that shows both the settings for the connection and the error but it fails to upload.
One Star

Re: MS SQL Server connection problem

Hi
I can't find the image.
Please notice the limits:20 images per post, each image must be less then 1024x768 pixels and 200 KB.
Regards,
Pedro
One Star

Re: MS SQL Server connection problem

I shall give you the info as per the screen.
One Star

Re: MS SQL Server connection problem

the input parameters are as follows
DB Type: Microsoft SQL Server
Login: sa
Password: ********
Server: MPDBS-HP\SQLEXPRESS
Port: 1433
Database: care_demo
One Star

Re: MS SQL Server connection problem

Hi
There are many possibilities why the connection fails.
First, please check the SQL Server Management Studio to check the port.
Then make sure the TCP/IP is on.
Or close the firewall.

Regards,
Pedro
One Star

Re: MS SQL Server connection problem

Thanks,I'll do that. This is all happening on a single, local machine, not over a network. The Port 1433 is the number Talend puts in by default.
One Star

Re: MS SQL Server connection problem

OK, TCP/IP is enabled and the port is set as the default 1433. Where next?
One Star

Re: MS SQL Server connection problem

Hi
The IPALL is 1433?
What's the error log?
Regards,
Pedro
One Star

Re: MS SQL Server connection problem

Hiya
Can't see IPALL, where do I find that?
The error in Talend is 'Connection Failure. You must change the Database Settings' Impossible to initialize the connection!'
thanks
Mike
One Star

Re: MS SQL Server connection problem

Hi Mike
You can find it in SQL SERVER Management Studio.
Regards,
Pedro
One Star

Re: MS SQL Server connection problem

Hi Pedro
I thougth there should be an IP address tab on the TCP/IP properties but there isn't one!
Mike
One Star

Re: MS SQL Server connection problem

Hi
It's here.
Regards,
Pedro
One Star

Re: MS SQL Server connection problem

Hi Pedro
I'm obviously missing something because I can't find that tab at all. Sorry to be a pain but can you tell me how you got to it?
thanks
Mike
One Star

Re: MS SQL Server connection problem

Hi Mike
Do you insall sql server on your local machine?
Is there a program called SQL Server Management Studio?
Please find more info on Google about JDBC sql server.
Regards,
Pedro
One Star

Re: MS SQL Server connection problem

Hi Pedro
Yes, I installed SQL Server on my local machine and I have SQL Server Management Studio. This is the first time I have had anything to do with JDBC and I think that maybe this isn't installed correctly. I'm not even sure where it should be installed. I've checked the documentation but I don't find it clear on how and where to install it.
Mike
One Star

Re: MS SQL Server connection problem

I've looked at all the documentation I've access to and Googled for help by still can't work out what is going on. I'm guessing that the answer lies with JDBC, but only guessing, but I'm out of my knowledge area here. Any idea where I can find what the correct JDBC installation for SQL Server/Talend should be?
Mike
One Star

Re: MS SQL Server connection problem

Hi Mike
Do you have any co-workers who are familiar with SQL Server JDBC?
Usually this is a little hard for novice.
Regards,
Pedro
One Star

Re: MS SQL Server connection problem

I shall be checking today. Is JDBC the only mechanism for Talend Tasks to talk to SQL Server?
thanks
Mike
One Star

Re: MS SQL Server connection problem

Hi Mike
Anather way is ODBC.
But if you are using 64bit system, ODBC might cause more error.
Regards,
Pedro
One Star

Re: MS SQL Server connection problem

OK thanks. I'll talk to my colleagues today asbout our options.
Mike
One Star

Re: MS SQL Server connection problem

Hi Mike,
If you are able to find out the issue, please post back. I am having the exact same issue where I am getting the Impossible to Initialize the connection.
Thanks!
One Star

Re: MS SQL Server connection problem

MIke, any solution? I also am trying to work through this error.
One Star

Re: MS SQL Server connection problem

i have a solution!! wow what a mess to find all the answers.
so...
i had installed Talend DI 64 bit, i think this is the first issue to an easy solution, tho have not taken the time to uninstall and only try 32bit.
1. verify version of Talend that was installed via- Help -> About Talend Studio -> Installation Details
2. from same location verified version of Java that Talend was using, since i had both 6 and 7 installed.
3. Download jtds-1.2.6
4. Copy "jtds-1.2.6.jar"
FROM C:\Users\...\Desktop\jtds-1.2.6-dist
TO C:\Program Files\Java\jre7\lib
5. Copy "ntlmauth.dll"
FROM C:\Users\...\Desktop\jtds-1.2.6-dist\x64\SSO
TO C:\Program Files\Java\jre7\bin
6. Restarted PC
7. Set TCP port 1433 in SQL Server Configuration Manager under SQL Server Network Configuration -> Protocols for Server -> TCP/IP -> IPAll -> TCP Port (for some reason this was blank)
8. Added Additional parameters to connection setup:
?Integrated Security?=true
One Star

Re: MS SQL Server connection problem

I am having exactly the same issue. Tried following the solution.
1. enabled tcp/ip port on MSSQL server at correct port
2. Since I am using 32 bit version, copied appropriate dll and jar file. Tried using both jtds-1.2.6-dist and jtds-1.3.0-dist but still getting following error
Connection failure. You must change the Database Settings.

java.lang.ClassCastException: net.sourceforge.jtds.jdbc.ConnectionJDBC3 cannot be cast to net.sourceforge.jtds.jdbc.ConnectionJDBC2
Any idea?
Thanks
Ap
One Star

Re: MS SQL Server connection problem

the input parameters are as follows
DB Type: Microsoft SQL Server
Login: sa
Password: ********
Server: MPDBS-HP\SQLEXPRESS
Port: 1433
Database: care_demo

Hi Mike,
Earlier i had the same issue but it solved. I believe you might have "SQL Server Management Studio"(ssms).
- Are you able to login with SQL Server Management Studio with the above login/password then there is a problem. Check your security setting of SQL Server(is it windows authentication mode (or) SQL Server and windows authentication mode(both). If the security setting is windows authentication mode then change to both modes(SQL Server and Windows authentication mode).
- One more thing you need to verify if you are not able to login then check the properties of login(sa) which you can go through Security->Properties->Status. Select option enable if it is disabled.
All these changes done only if you are administrator user.
The above one is few reasons which you cannot connect sql server.
Regards
Arshad
One Star

Re: MS SQL Server connection problem

the input parameters are as follows
DB Type: Microsoft SQL Server
Login: sa
Password: ********
Server: MPDBS-HP\SQLEXPRESS
Port: 1433
Database: care_demo

Hi Mike,
Earlier i had the same issue but it solved. I believe you might have "SQL Server Management Studio"(ssms).
- Are you able to login with SQL Server Management Studio with the above login/password "if not" then there is a problem. Check your security setting of SQL Server(is it windows authentication mode (or) SQL Server and windows authentication mode(both). If the security setting is windows authentication mode then change to both modes(SQL Server and Windows authentication mode).
- One more thing you need to verify if you are not able to login then check the properties of login(sa) which you can go through Security->Properties->Status. Select option enable if it is disabled.
All these changes done only if you are administrator user.
The above one is few reasons which you cannot connect sql server.
Regards
Arshad
update in the above sentence.
Six Stars

Re: MS SQL Server connection problem

Hi trevmetz,
Your steps did work for me. Thanks a lot for sharing the steps.
Cheers!
Chanaka