Can i connect to SQLExpress instance using (local)

Hello,

I am using the following connection string to connect to SQL Server 2005 Express Edition installed on my local PC.
string sqlstr = "Server=(local);Database=MyDB;User ID=sa;Password=sa;Trusted_Connection=False";

And it gives me the following error. The string works fine in computer with SQL Server 2000 instance but fails with 2005. The string also fails for "Server = localhost".
However the string works very fine if I use the name of Server 2005 i.e ./SQLExpress

===================================

Cannot connect to (local).

===================================

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

Error Number: 2
Severity: 20
State: 0

Now the main problem is that, of course I could use a named instance, but since I am embedding the Connection String in a dll file so that any user can use it to connect it to the local instance of the SQL Server 2005 installed in his/her computer, I need to use "server = localhost". Doesn't SQL 2005 support this. If yes, where could i find option to enable it?

I have enabled every option available in surface area configuration for SQL Server 2005, but nothing seems to help out. I even use local as well as remote connections using both TCP/IP and named pipes.
Can nyone help me out?

By default SQL Server 2005 Express edition is installed as a named instance with name "SQLExpress". As a result, the connection string should specify the instance, e.g. ".\SQLExpress" or "(local)\SQLExpress" to distinguish it from a possible default instance of SQL Server that may be installed on the same machine. If you install SQL Express as a default instance then "." or "(local)" are the proper ways to specify in the connection string.|||Is there a way to convert the named instance to a default one apart from re-installation. I have only a single named instance on my computer.|||The only way to change the instance name is to re-install - uninstall and install as default instance.|||Thanks a lot, Peter. That solves it all.sql

Can connect to sqlexpress via asp.net but not via asp

I can connect to a sqlexpress database using aspx (asp.net 2.0) pages
but not via classic asp pages. Both the asp and aspx pages are within
the same website. The sqlexpress database is also on the same server.
I have tried using a variety of connection strings within my asp pages,
including dsn, dsn-less, windows authentication, sql authentication
etc. (The sql express database by the way has mixed authenatication and
both work fine)
The errors I am getting are the following:
1. When I put a windows authentication string in my asp
page I get this error:
************
Provider error '80040e21'
Multiple-step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done.
?, line 0
***************
2. When I use a sql authenticaion string, I get this
error:
********************
Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not
found and no default driver specified
?, line 0
**********************
3. When I use a DSN connection string (with the DSN
connecting successfully within the ODBC utility) I get
this error:
******************
Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
open database "xxxx" requested by the login. The login
failed.
?, line 0
***********************
As mentioned before I have no problems in connecting to
the database via aspx pages within the same website. I can also
successfully connect to the database via visual web developer or sql
express manager on the same machine.
Any ideas?
Sorry, solved this!
|||You took the time to post that you found an answer, could you possibly post the solution?
~Another Steve

Quote:

Originally posted by Steve
Sorry, solved this!

Can connect to sqlexpress via asp.net but not via asp

I can connect to a sqlexpress database using aspx (asp.net 2.0) pages
but not via classic asp pages. Both the asp and aspx pages are within
the same website. The sqlexpress database is also on the same server.
I have tried using a variety of connection strings within my asp pages,
including dsn, dsn-less, windows authentication, sql authentication
etc. (The sql express database by the way has mixed authenatication and
both work fine)
The errors I am getting are the following:
1. When I put a windows authentication string in my asp
page I get this error:
************
Provider error '80040e21'
Multiple-step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done.
?, line 0
***************
2. When I use a sql authenticaion string, I get this
error:
********************
Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not
found and no default driver specified
?, line 0
**********************
3. When I use a DSN connection string (with the DSN
connecting successfully within the ODBC utility) I get
this error:
******************
Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
open database "xxxx" requested by the login. The login
failed.
?, line 0
***********************
As mentioned before I have no problems in connecting to
the database via aspx pages within the same website. I can also
successfully connect to the database via visual web developer or sql
express manager on the same machine.
Any ideas?Sorry, solved this!|||You took the time to post that you found an answer, could you possibly
post the solution?
~Another Steve
Steve wrote:
> *Sorry, solved this! *
larmores
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1889178.html

Can connect to sqlexpress but not the DB ?

whenever i try and choose the database i wish to work with like below. I end up with an error message about the user not being associated with a trusted sql server connection

cnStr = @."Data Source=server\SQLEXPRESS;Initial Catalog=sqldata;User ID=blah\me";

same with this one

cnStr = @."Data Source=STOCKYARD\SQLEXPRESS;Initial Catalog=DB;Trusted_Connection=Yes;";

i figured this was all permission based so i kept dorking around with security adding my user name to anything i could find, and also went through and dozen or so connection strings and found when i take out the DB name that i can open a connection.... as the code below works...

cnStr = @."Data Source=Server\SQLEXPRESS;Trusted_Connection=Yes;";

cn.Open();

I plan on importing another database so how will i later on choose between the two, i notice i can create logins only at the security for the sqlexpress and not for my database? did i mess up how i created the database?
i have added my username and given permissions to the security for the database i wish to connect to, but is there another step im missing? i would keep trucking but i would like to have multiple DB on the sql express and pick and choose at the code level, maybe im misunderstanding what the catalog should include or i can change the connections database at another point?

You need to have specific permissions in a database given to a Database User, and that user needs to be Associated with a Server Login. My guess is that you don't quite have things connected together correctly yet. Check ou this BOL topic that helps lay out how Windows Users, SQL Logins and Database Users all fit together.

Once you have things associated together, you should be able to work in a database.

Mike

|||

You cannot use "User ID=blah\me". If you are trying to use your domain account, just use "Trusted_Connection=Yes". User ID is used for SQL Authentication, and you cannot specify a domain account.

You must have login permission for the "Initial Catalog=DB", otherwise, you won't be able to login. Logins are for server and user id is for database. You can more inside here: http://builder.com.com/5100-6388-5055543.html

some quote:

<<<<While logins belong to servers, users belong to databases. A user ID identifies a particular user of
a particular database. Also, users are specific to databases—that is, user Fred in the Northwind database
is not the same as user Fred in the pubs database, although both Freds may be associated with the same login.>>>>>

Can connect to sql server from some machines but not others...

I have a server with a sqlexpress database on it.
It has 'Allow Remote Connections' checked

It has the Browser Service enabled and started utilizing Surface Area Configuration

It has Local and Remote Connections Using both TCP/IP and named Pipes.

I have used http://support.microsoft.com/kb/914277

And one machine can connect to it fine.. .

but others can't?

Any Ideas?

Casey

Hi Casey,

We'll need the exact error message that you're seeing to be able to help you, as it can be a number of reasons.

|||

PS. I can ping the server from both computers (the one that can connect and the one that cannot)

TITLE: Connect to Server

Cannot connect to HOZZZZZZZ\SQLEXPRESS.

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

BUTTONS:

OK

|||

you have already said that

(a) Remote connection enabled

(b) Browser service running

(c) TCP/IP protocol selected

the only one thing left is check for any Firewall enabled on this server. If yes create exception

also makesure the service is running

Madhu

|||

Yes, we have checked that the service is running. (In SQL Server Configuration Manager when I click SQL Sever 2005 Services, both Sql Server and Sql Server Browser show state as 'Running' and Start Mode as 'Automatic)

Yes, there is no firewall runnning, so no need for exceptions.

(when I run firewall.cpl from command I get message box saying that the WIndows Firewall/ ICS service is not running.)

And this is why we can connect to this sql from some machines.. but not sure why we can't from others..

|||

Can you connect if you specify ip instead of machine name? (e.g. 127.0.0.1\SQLEXPRESS)

If two of these machines are on the same domain, they would be able to connect with <single part name>\instance, but a machine outside of the domain would need either ip\instance or <fully qualified dns name>\instance.

Hope we can figure this out.

John

|||

Actually, error 26 indicates that you've established the transport connection but cannot locate the named instance to which you want to connect. Is your client machine Vista by any chance?

Il-Sung.

|||

IP Name didn't work. (or fully qulalified name)

However we removed the \SQLEXPRESS and it seems to be working. (I double checked and the SqlServer configuration manager on the server does show that the it is running as a SQLEXPRESS Install.)

so Server = HOZZZZ\SQLEXPRESS - Doesn't work...

but Server = HOZZZZ - Does seem to work.. don't understand that one..

casey

|||

Well if the named one did not work, you either have a client alias configured on your server or you have a default non-Express instance on your server. Connect to the instance and use the SELECT @.@.version to find out more details about the version of SQL Server.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Again, are you using Vista?

|||

The connection string without sqlexpress appears to be working.

Windows XP

select@.@.version =

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)Oct 14 2005 00:33:37Copyright (c) 1988-2005 Microsoft CorporationExpress Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Casey

|||

Then the only explanation for me is that you are using a alias on the client side which points to the .\SQLExpress instance.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||I think the more likely event is that you clicked "default instance" when you installed SQL Express.

This may sound a bit confusing but if you click nothing on the instance selection screen but the "Next" button, SQL Express installs as a named instance titled SQLExpress. If you click "default instance", it will install as a "default instance" which has a different meaning.

In the global context, a default instance is run as MSSQLSERVER. The connection string to connect to a default instance is "machinename" with no instance name at the tail. For any named instance (including SQL Express's default named instance) you have to append \instancename, so it looks like "machinename\instancename".

The reason this is all confusing is that by default, SQL Express installs as a named instance, but if you click "Default Instance" it installs as a default instance (which is not what the default behavior was to begin with).

In a global context, the "Default Instance" is a special, unnamed instance that is the default for other SKUs of the product. This doesn't track with the local definition of "default instance" which, of course, should be the default on the screen (SQLExpress).

I hope that made sense and helps to clarify what might have been going on here,

John