One Star

Error when trying to connect MS SQL Server 2005

When I configure the MS SQL Server connection I have this error :
"I/O ErrorSmiley FrustratedSO Failed: Native SSPI library not loaded. Check the java.library.path system property"
I still have the same problem in MS SQL Server Management Studio Express when I need to see Properties for a table in a Database.
Thank you for your help...

  • Data Integration
33 REPLIES
Four Stars

Re: Error when trying to connect MS SQL Server 2005

With regard to making MS SQL connections from TOS, I believe that you need to use SQL users/pwds for connections. Windows authentication is not currently supported. Is your server set to "SQL Server and Windows Authentication mode"?
As far as having that error in the management studio, not sure on that since I don't use the Express version.
- Brian
One Star

Re: Error when trying to connect MS SQL Server 2005

Hi All,
I am using talend open studio but when i create the MS SQL Server connection then talend gives me
error "host name is not found". My sql server 2005 name is : "SSS\SQLEXPRESS" and when i
filled this name in host name so error is coming "Connection failure. You must change the Database Settings.
Unknown server host name 'SSS\SQLEXPRESS'.
".
plz solve my issue .
Thanks,
SSS
Email : syed2uk@gmail.com
One Star

Re: Error when trying to connect MS SQL Server 2005

Hi All,
I am using talend open studio but when i create the MS SQL Server connection then talend gives me
error "host name is not found". My sql server 2005 name is : "SSS\SQLEXPRESS" and when i
filled this name in host name so error is coming "Connection failure. You must change the Database Settings.
Unknown server host name 'SSS\SQLEXPRESS'.
".
plz solve my issue .
Thanks,
SSS
Email : syed2uk@gmail.com
Four Stars

Re: Error when trying to connect MS SQL Server 2005

To use a MSSQL named instance in TOS, you only put the server name in the host field. The instance name goes in the Additional Parameters field. So you should try:
Login: yourDBlogin
Password: yourDBpwd
Server: SSS
Port: 1433
Database: yourdbname
Schema: dbo
Additional Parameters: instance=SQLEXPRESS

- Brian
One Star

Re: Error when trying to connect MS SQL Server 2005

Hi All,
I am using talend open studio but when I create the MS SQL Server connection then talend gives me
error "host name is not found" . My sql server 2005 name is : "SYED-PC\JAZZSQL" and when I
fill this name in server field then error is coming "Connection failure. You must change the Database Settings.
Unknown server host name 'SYED-PC\SQLEXPRESS'.
". I have been tried with only ?JAZZSQL? and with instance name but still error is coming.
plz solve my issue and give me result with snapshot plz.
Thanks,
SSS
Email : syed2uk@gmail.com
Four Stars

Re: Error when trying to connect MS SQL Server 2005

You'll need to specify your own db login, password and database name accordingly.
- Brian
One Star

Re: Error when trying to connect MS SQL Server 2005

Thanks very much briansms..

Take care ,
Bye bye
Syed
One Star

Re: Error when trying to connect MS SQL Server 2005

Thanks very much
engelm
One Star jb
One Star

Re: Error when trying to connect MS SQL Server 2005

i have a similiar problem, the connection don't know my data base user (login) Smiley Frustrated
some idea??
thanks
Four Stars

Re: Error when trying to connect MS SQL Server 2005

jb,
You need a login and password. Are you trying to access a local SQL Server on your PC or one on a server?
If you have a local install, you were prompted to specify a password for the sa user when installing the product.
How do you access the database with the Query Analyzer/SQL Management Studio?
- Brian
One Star

Re: Error when trying to connect MS SQL Server 2005

Hi there,
I also had some issues with MSSQL connections. And I solved these combining different answers I found here and there.
As I ended up on this forum like you, I'll post my findings so that people having trouble related to specific MSSQL anarchy could
hopefully find the answers they really need.
Some steps need to be taken care of before filling the TOS form as briansms said.
These are quite MSSQL related topics, so you may already have accomplished something before in those areas.
Connections with user/password are necessary when using the jdbc connector with TOS. If MS SQL Server 2005 is originally configured for Windows authentication, you need to enable the remote access capabilities (mixed mode or SQL Server authentication).
This needs a little more extra work, as follows:

1. Configure some stuff with SQL Server Management Studio
-----------------------------------------------------------------------
Start MSSMS : StartMenu -> ?All Programs? -> ?Microsoft SQL Server 2005? -> ?Microsoft SQL Server Management Studio?
Log in to SQL Server 2005 with windows authentication or "sa" user;
Highlight the server and right click ?Properties?;
Highlight ?Security? -> Under ?Server authentication?, choose ?SQL Server and Windows authentication mode?;
Highlight ?Connections? -> Under ?Remote server connections?, choose ?Allow remote connections to this server?;
Click OK to save the settings.

2. Configure more stuff in SQL Server Surface Area Configuration
--------------------------------------------------------------------
Start da beauty: ?All Programs? -> ?Microsoft SQL Server 2005? -> ?Configuration Tools? -> ?SQL Server Surface Area Configuration?
Click ?Surface Area Configuration for Services and Connections? -> ?MSSQLSERVER? -> ?Database engine? -> ?Remote connections ?
Under ?Local and remote connections?, choose at least ?Using TCP/IP only? (if you need to access the server through pipes, there is something for you as well)
Click ?Apply? to save the settings. You're brave.
3. Stop and restart the SQL server
-------------------------------------------------
In order to refresh your settings, restart the server using ?SQL Server Management Studio?, Services in Control Panel or the ?SQL Server Configuration Manager". Use "Stop" and "Start" functions or "Restart" when available.
4. Great! Before complaining, check that everything is OK now
------------------------------------------------------------------------
Start the config manager under ?All Programs? -> ?Microsoft SQL Server 2005? -> ?Configuration Tools? -> ?SQL Server Configuration Manager"
First, be sure that your service "SQL Server (SQLEXPRESS)" is up and running under "SQL Server 2005 Services"
Then, click "SQL Native Client Configuration" -> "Client Protocols" and double-click on TCP/IP.
Your protocol must be enabled and the "Default Port" must been set (usually to 1433)
5. Create credentials
------------------------
If you didn't already do so, create a login through ?Security? -> ?Logins? -> ?New Logins?? using the ?SQL Server Management Studio?.
Add a database under "Databases".
And create a user ("Security"->"Users"->"New User...") under this database, that is referencing the newly created login.
Disconnect.
And try first to log in with the newly created user, setting ?Authentication? -> ?SQL Server authentication? this time.
If everything is OK, you will be able to connect in TOS, as briansms already said:
Login: yourLogin
Password: yourPass
Server: localhost
Port: 1433
Database: yourDB
Schema:
Additional Parameters: instance=SQLEXPRESS
BTW, I'm using MSSQL 2005 on a local server, but use whatever hostname you need.
Also SQLEXPRESS is the default instance name, but you may have something else instead.
In order to use the jdbc connector, you'll need to specify this one as an additional parameter, not like localhost/SQLEXPRESS, as the mssql site claims.

Cheers!
One Star

Re: Error when trying to connect MS SQL Server 2005

Greetings.
I seem to be getting this problem and i don't know how to deal with it. I try to connect with Microsoft sql server on talend
unable to get information from sql server
Moderator

Re: Error when trying to connect MS SQL Server 2005

Hi Gudies,
What's the studio build version are you using? Can you connect to your sql server directly with DB tool, like DB Visualizer without talend?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error when trying to connect MS SQL Server 2005

Hi xdshi,
The studio version am using on talend is V5.4.1. I can connect to my sql server directly with DB tools.
One Star

Re: Error when trying to connect MS SQL Server 2005

I need help. How do i filter fields in a csv which cotain null values and once's which have don't have value on them. Am using tmap but when i try to write the files into a database i get and error that reads "For input string: null. Don't know what to do at this point. Need help guys.
Moderator

Re: Error when trying to connect MS SQL Server 2005

Hi,
The studio version am using on talend is V5.4.1. I can connect to my sql server directly with DB tools.

Sorry for delay.
What's your sqlserver authentication?
Usually there are two ways to connect to sqlsever: sql server authentication and windows authentication
If you use windows authentication , you need put ntlmauth.dll (library for windows authentication)into
c:\windows\system32 and/.
Restart talend product.
Let me know if it is OK with you.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Moderator

Re: Error when trying to connect MS SQL Server 2005

Hi Gudies,
I need help. How do i filter fields in a csv which cotain null values and once's which have don't have value on them. Am using tmap but when i try to write the files into a database i get and error that reads "For input string: null. Don't know what to do at this point. Need help guys.

How did you set your input file schema? Could you please take a look at the related forum : topic For input string: "null"].
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error when trying to connect MS SQL Server 2005

I was very grateful to find the post by dream4ge, which brought together answers from many places. I have one further step to add as follows:
Step 0. Ensure the windows service "SQL Server Browser" is running. To do this, first run "services.msc", right click on "SQL Server Browser" and choose Properties. Set the startup type to "Automatic", press "Start" and press "OK". The SQL Server Browser service is now listening on port 1434 and non-default instances (such as SQLEXPRESS) are now supported - when Talend asks to connect to SQLExpress or another non-default instance, this service will redirect Talend to the appropriate port.
Now you've done this, follow dream4ge's steps:
Hi there,
I also had some issues with MSSQL connections. And I solved these combining different answers I found here and there.
As I ended up on this forum like you, I'll post my findings so that people having trouble related to specific MSSQL anarchy could
hopefully find the answers they really need.
Some steps need to be taken care of before filling the TOS form as briansms said.
These are quite MSSQL related topics, so you may already have accomplished something before in those areas.
Connections with user/password are necessary when using the jdbc connector with TOS. If MS SQL Server 2005 is originally configured for Windows authentication, you need to enable the remote access capabilities (mixed mode or SQL Server authentication).
This needs a little more extra work, as follows:

1. Configure some stuff with SQL Server Management Studio
-----------------------------------------------------------------------
Start MSSMS : StartMenu -> ?All Programs? -> ?Microsoft SQL Server 2005? -> ?Microsoft SQL Server Management Studio?
Log in to SQL Server 2005 with windows authentication or "sa" user;
Highlight the server and right click ?Properties?;
Highlight ?Security? -> Under ?Server authentication?, choose ?SQL Server and Windows authentication mode?;
Highlight ?Connections? -> Under ?Remote server connections?, choose ?Allow remote connections to this server?;
Click OK to save the settings.

2. Configure more stuff in SQL Server Surface Area Configuration
--------------------------------------------------------------------
Start da beauty: ?All Programs? -> ?Microsoft SQL Server 2005? -> ?Configuration Tools? -> ?SQL Server Surface Area Configuration?
Click ?Surface Area Configuration for Services and Connections? -> ?MSSQLSERVER? -> ?Database engine? -> ?Remote connections ?
Under ?Local and remote connections?, choose at least ?Using TCP/IP only? (if you need to access the server through pipes, there is something for you as well)
Click ?Apply? to save the settings. You're brave.
3. Stop and restart the SQL server
-------------------------------------------------
In order to refresh your settings, restart the server using ?SQL Server Management Studio?, Services in Control Panel or the ?SQL Server Configuration Manager". Use "Stop" and "Start" functions or "Restart" when available.
4. Great! Before complaining, check that everything is OK now
------------------------------------------------------------------------
Start the config manager under ?All Programs? -> ?Microsoft SQL Server 2005? -> ?Configuration Tools? -> ?SQL Server Configuration Manager"
First, be sure that your service "SQL Server (SQLEXPRESS)" is up and running under "SQL Server 2005 Services"
Then, click "SQL Native Client Configuration" -> "Client Protocols" and double-click on TCP/IP.
Your protocol must be enabled and the "Default Port" must been set (usually to 1433)
5. Create credentials
------------------------
If you didn't already do so, create a login through ?Security? -> ?Logins? -> ?New Logins?? using the ?SQL Server Management Studio?.
Add a database under "Databases".
And create a user ("Security"->"Users"->"New User...") under this database, that is referencing the newly created login.
Disconnect.
And try first to log in with the newly created user, setting ?Authentication? -> ?SQL Server authentication? this time.
If everything is OK, you will be able to connect in TOS, as briansms already said:
Login: yourLogin
Password: yourPass
Server: localhost
Port: 1433
Database: yourDB
Schema:
Additional Parameters: instance=SQLEXPRESS
BTW, I'm using MSSQL 2005 on a local server, but use whatever hostname you need.
Also SQLEXPRESS is the default instance name, but you may have something else instead.
In order to use the jdbc connector, you'll need to specify this one as an additional parameter, not like localhost/SQLEXPRESS, as the mssql site claims.

Cheers!
One Star

Re: Error when trying to connect MS SQL Server 2005

I put:
Username: sa
password: ****
Server: Khyati
Port: 1433
DA: Talend
Additional parameters: instance=SQLEXPRESS

I have created the db 'Talend' in SQL SERVER Management Studio. Port is enabled in TCP/IP and is 1433. What else do I need to do?
I am getting an error: Connection failure. You must change the database settings. Server 'Khyati' has no instance named 'SQLEXPRESS'.
Moderator

Re: Error when trying to connect MS SQL Server 2005

Hi 28Khyati,
Did you restart your sql server brower? Can you connect to your sql server express without using Talend tool.

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error when trying to connect MS SQL Server 2005

Hi Sabrina,
Yes, I did that successfully too. 
Thanks
Khyati
One Star

Re: Error when trying to connect MS SQL Server 2005

xdshiHi,
The studio version am using on talend is  V5.4.1. I can connect to my sql server directly with DB tools.

Sorry for delay.
What's your sqlserver authentication?
Usually there are two ways to connect to sqlsever: sql server authentication and windows authentication
If you use windows authentication , you need put ntlmauth.dll (library for windows authentication)into
c:\windows\system32 and/.
Restart talend product.
Let me know if it is OK with you.
Best regards
Sabrina
One Star

Re: Error when trying to connect MS SQL Server 2005

Please provide solution  without using "ntlnmauth.dll" how to connect MSSql server(Windows authentication mode) with help of tMSSQL_Connection.
 because I don't have permission for "System32"
One Star

Re: Error when trying to connect MS SQL Server 2005

Hi all, i am using the Talend 6.1 in  CentOs and I want to store the oracle db data to MS Sql server table in different machine with windows 10 OS.
What are the steps i need to follow can anyone help me...
Thanks and Regards
 
Moderator

Re: Error when trying to connect MS SQL Server 2005

Hi,
With talend open source, if you want to migrate  oracle db data to MS Sql server table, you need to create many subjobs for each table, each subjob is used to migrate data from one table to another table. If the table does not exist in Mssql DB, you can create it by checking the option 'create table if not exist' in the action on table list on tMssqlOutput component.

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error when trying to connect MS SQL Server 2005

Hi,
I added "ntlmauth.dll" to c:\windows\system32 and .
Didn't help. Still timeout error message:
Connection failure. You must change the Database Settings.
java.lang.RuntimeException: java.sql.SQLException: Login timed out.
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:195)
....
Caused by: java.sql.SQLException: Login timed out.

Any ideas?

I use:
Login:
Password:
Server: LAPTOP_ELQ2Q8QT
Port: 1433
Database:
Schema: 
Additional Parameters: instance=SQLEXPRESS
Moderator

Re: Error when trying to connect MS SQL Server 2005

Hi,
Can you successfully connect to your mssql server by client without using talend tool? On which build version you got this issue?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Error when trying to connect MS SQL Server 2005

Yes. I'm able to connect to MS SQL server via Microsoft SQL server management studio.
The issue is on built 6.2.1
Dan
Moderator

Re: Error when trying to connect MS SQL Server 2005

Hi,
Are you trying to connect local SQL server or remote?
If you are trying to connect local SQL server, have you tried to use localhost or 127.0.0.1 to see if the connection is Ok?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.