Connection String - SQL Server Express

R

Ross

I am trying to establish and ADODB connection from Access 2003 to SQL Server
Express 2008.

This is the Syntax format that I found:

Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=
Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Here is my vba code:

cn.Open "Provider=SQLOLEDB;Data Source=OWNER-PC\SQLEXPRESS;Initial
Catalog=ReportingDB;UserId=Owner-PC\Owner;Password=pswd2;"

My error is: "Invalid connection string Attribute"

How can I find and fix the error?

Many Thanks

Ross
 
S

Sylvain Lafontaine

First, you need to put a space in "User Id", second, the User Id won't work
with a Windows Account but only with a SQL-Server Login Account. If you
want to use a Windows account, you must use a Trusted Connection (ie, set
the Integrated Security to SSPI without setting both the the User Id and the
Password because the current credential will be used exclusively; see
http://www.connectionstrings.com/sql-server-2008 ).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
S

Sylvain Lafontaine

First, you need to put a space in "User Id", second, the User Id won't work
with a Windows Account but only with a SQL-Server Login Account. If you
want to use a Windows account, you must use a Trusted Connection (ie, set
the Integrated Security to SSPI without setting both the the User Id and the
Password because the current credential will be used exclusively; see
http://www.connectionstrings.com/sql-server-2008 ).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
R

Ross

Sylvain,

Thank you for this response. My new install SQL server express was wet up
with windows authentication. I simply added SQL server authentication and my
string worked perfectly (THE FIRST TIME)!

Thank you again.

Ross
 
R

Ross

Sylvain,

Thank you for this response. My new install SQL server express was wet up
with windows authentication. I simply added SQL server authentication and my
string worked perfectly (THE FIRST TIME)!

Thank you again.

Ross
 
Joined
Mar 10, 2013
Messages
1
Reaction score
0
I am having a similar problem. I would like to connect to a sql server installed in my local machine. I am using windows authentication to connect.

the string I have is below, but does not seem to get me connected.

"Provider=SQLOLEDB;Data Source=localhost\sqlexpress;Initial Catalog=RnrBooks;Integrated Security=SSPI;User ID=spike_laptop-PC\spike_laptop;Password=;"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top