connection strings, trusted-connection VS username-password, SQL Server 2005 Express

All --

Please help.

I have some questions about connection strings.

BACKGROUND...

Note that I am using the "SQL Native Client OLE DB Provider", SQL Sever 2005 Express, ASP.NET, C#.NET, and VS.NET 2005.Note that I do not want to use the "Attach a database file" type of connection string.Note that I am using the sitehttp://www.ConnectionStrings.com as a reference.Note that this "Standard security" connection string...
<add name="LocalSqlServer" connectionString="Server=MYTEST\OFFICESERVERS;Database=MyDatabase;Uid=MyDatabaseUser;Pwd=MyDatabasePassword;" />
...does not work and causes the following run-time error...
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)Note that this "Trusted connection" connection string...
<add name="LocalSqlServer" connectionString="Server=MYTEST\OFFICESERVERS;Database=MyDatabase;Trusted_Connection=yes;" />
...works but is less-than ideal because it uses a Trusted Connection rather than a Username/Password combination.

QUESTIONS...

(A) What exactly does the error in Item 4 mean?

(B) Do 4 and 5 imply that one must use a Trusted_Connection for all SQL Server 2005 Express connection strings when the database does not reside in the web application's App_Data folder or use a "Attach a database file" type of connection string?

(C) Are there any alternatives?

(D) What do you suggest?

Please advise.

Thank you.

-- Mark Kamoski

Hi Mark,

Here are the answers to your questions:

(A). The error message means a client connecting to SQL Server may encounter this named pipe error when connecting to SQL Server if the server has not enabled named pipe support even though other protocols such as TCP/IP are available.

(B). You are not required to use a Trusted_Connection if you have enabled SQL Authentication on the SQL Server.

(C)& (D) I would suggest you to try the connection again after a while. If that doesn't help and you're always getting this error, please check the following articles for troubleshoot.

http://msdn2.microsoft.com/en-us/library/ms175496.aspx
http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

Connection problems

Am working on a SQL Server 2000 db with Powerbuilder 8 as the frontend. For
months our endusers connected thru the SQL Server native driver for version
6. Now, have just ported the application to Powerbuilder 10, and I
understand that the native drivers for SQL Server 2000 no longer exist in
Powerbuilder, and you are expected to use OLEDB or ODBC. Thinking that
OLEDB was the preferred method, I attempted to connect to the Server with
this Profile in Powerbuilder. I could connect fine, but it frooze everyone
else out. Anyone else have this problem, and if, how did you resolve it?
So, some more questions:
1). What is the preferred way to connect to SQL Server 2000, OLEDB or ODBC.
2). What is the fastest connection?William F. O'Neill (wfoneill@.bellsouth.net) writes:
> Am working on a SQL Server 2000 db with Powerbuilder 8 as the frontend.
> For months our endusers connected thru the SQL Server native driver for
> version 6. Now, have just ported the application to Powerbuilder 10,
> and I understand that the native drivers for SQL Server 2000 no longer
> exist in Powerbuilder, and you are expected to use OLEDB or ODBC.

I suppose that what you call "native drivers" is the old DB-Library
API, that I know that older versions of Powerbuilder used. MS has
deprecated this API, and have not made any development to it since
1998, so with DB-Lib you lack support for a lot of new stuff. All reason
to change.

> Thinking that OLEDB was the preferred method, I attempted to connect to
> the Server with this Profile in Powerbuilder. I could connect fine, but
> it frooze everyone else out. Anyone else have this problem, and if, how
> did you resolve it? So, some more questions:
> 1). What is the preferred way to connect to SQL Server 2000, OLEDB or
> ODBC.
> 2). What is the fastest connection?

It's not that OLE DB locks up the database by its own. There must be
something to it, but with zero knowledge of your environment, I have no
clue what it might be.

My prime choice would be SQLOLEDB to connect. But I need to add the
disclaimer that I have no experience of Powerbuilder, and it depends a
little on the API that Powerbuilder gives you. What you should not
use is OLE DB over ODBC, but make sure that you have Provider=SQLOLEDB
in your connectstring.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp