topic Re: Error when trying to connect MS SQL Server 2005 in Talend Studio

Error when trying to connect MS SQL Server 2005

Anonymous — Sat, 16 Nov 2024 14:14:45 GMT

When I configure the MS SQL Server connection I have this error :
"I/O ErrorSO 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...

Re: Error when trying to connect MS SQL Server 2005

briansms — Tue, 02 Sep 2008 17:50:44 GMT

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

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Fri, 05 Sep 2008 09:03:28 GMT

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

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Fri, 05 Sep 2008 09:04:45 GMT

Re: Error when trying to connect MS SQL Server 2005

briansms — Fri, 05 Sep 2008 12:15:13 GMT

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

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Sat, 06 Sep 2008 20:44:07 GMT

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

Re: Error when trying to connect MS SQL Server 2005

briansms — Mon, 08 Sep 2008 01:32:44 GMT

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

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Mon, 08 Sep 2008 04:12:56 GMT

Thanks very much briansms..

Take care ,
Bye bye
Syed

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Thu, 29 Jan 2009 15:41:48 GMT

Thanks very much
engelm

Re: Error when trying to connect MS SQL Server 2005

jb7 — Thu, 16 Jul 2009 11:17:12 GMT

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

Re: Error when trying to connect MS SQL Server 2005

briansms — Thu, 16 Jul 2009 22:04:34 GMT

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

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Thu, 09 Feb 2012 22:13:49 GMT

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!

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Wed, 19 Feb 2014 22:17:48 GMT

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

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Thu, 20 Feb 2014 03:56:25 GMT

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

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Thu, 20 Feb 2014 10:23:43 GMT

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

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Mon, 24 Feb 2014 23:34:48 GMT

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.

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Mon, 10 Mar 2014 03:58:43 GMT

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

Re: Error when trying to connect MS SQL Server 2005

Anonymous — Mon, 10 Mar 2014 04:16:54 GMT

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 https://community.talend.com/t5/Design-and-Development/For-input-string-quot-null-quot/td-p/88495.
Best regards
Sabrina

Re: Error when trying to connect MS SQL Server 2005

_AnonymousUser — Mon, 14 Apr 2014 23:11:44 GMT

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!

Re: Error when trying to connect MS SQL Server 2005

_AnonymousUser — Fri, 10 Apr 2015 05:27:49 GMT

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'.