One Star

Struggling to connect to a SQL Server 2008 R2 Express named instance

Hi there,
I'm trying to create my first DB connection via the wizard, but I can't seem to connect.
SQL Server is running locally on my laptop; Open Profiler is also installed on my laptop.
My computer name is 'Carl-THINK'
The instance name is 'SQLExpress'
SQL Server is running in mixed authentication mode and the 'sa' account is enabled.
I'm not sure how to determine what port number the instance is running on?
I'm trying to connect to the 'AdventureWorks2008R2' database.
Please help... thanks,
Carl

  • Data Integration
9 REPLIES
One Star

Re: Struggling to connect to a SQL Server 2008 R2 Express named instance

You need to give scrreenshot of settings you enter via DB connection wizard.
To get port number of your sql server you can use sql server configuration manager -->protocols for sqlexpress->TCP/TP. This gives you ports and ip address. But you need to share what inputs you are giving to various fields in wizard to get a clear picture of what is going wrong
One Star

Re: Struggling to connect to a SQL Server 2008 R2 Express named instance

thnx... pix attached
One Star

Re: Struggling to connect to a SQL Server 2008 R2 Express named instance

Hi,
You can follow steps given below to resolve issue
a) Through SQL server configuration manager ?Enable? the SQL Server Network Configurations and SQL Native Client 10.0 Configuration. See to that all the protocols are enabled except VIA.
b) Open SQL Server Management studio and right click ? properties on Database Connection. This will open properties window. Enable mixed mode authentication mode as shown in the figure below.
c) Go to Security ? Logins ? sa user and change the password for user sa.
d) Restart SQL Server Service and verify for the access
e) Create a new database connection in Metadata and enter required details
f) If you want to use another database user, and then add a user in sql server, assign a new password.
Default port is 1433
Thanks
Vaibhav
One Star

Re: Struggling to connect to a SQL Server 2008 R2 Express named instance

not working for me. tried both 32 and 64 bit (working on 64 bit machine).
accessing SQL server through management studio with the given user does work.
So must be a glitch in the connection....
tried the jdbc and odbc connection, still no luck.
One Star

Re: Struggling to connect to a SQL Server 2008 R2 Express named instance

You must specify the instance name of the Microsoft SQL Server in your database component.
in Additional Parameters put instance=SQLExpress
One Star

Re: Struggling to connect to a SQL Server 2008 R2 Express named instance

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

Re: Struggling to connect to a SQL Server 2008 R2 Express named instance

Does anyone know the solution to the above query? I could successfully activate the SQL Server Browser and login on SQL Server database through SQLExpress. Please let me know what else do I need to change.
Thanks
Khyati
One Star

Re: Struggling to connect to a SQL Server 2008 R2 Express named instance

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

This solution works for me. Thank You.
@Khyati -> Maybe you should IP Address instead computer name
One Star

Re: Struggling to connect to a SQL Server 2008 R2 Express named instance

Make sure to enable "TCP/IP" protocol under SQL Server Network Configuration.