Error when trying to connect MS SQL Server 2005

[Anonymous]

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

[briansms]

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

[Anonymous]

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!

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[_AnonymousUser]

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!

[_AnonymousUser]

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