Dot Net 2.0 or SQL Server 2005 problem?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have rather hastily installed VB 2005 Express and SQL Server 2005 Express
on my machine and I haven't much experience with either. So far as I can
remember I have mostly used default settings. SQL Exp. is set to use Windows
validation. I already have SQL Server 2000 Developer installed so my SQL
2005 Exp. instance is called SQLEXPRESS. I'm not sure whether I've forgotten
how to code or I have a server problem.

As a test, I am trying a basic connect with ADO.Net and I am getting
nowhere. Here is the code.

c.ConnectionString = "Data Source=SQLEXPRESS;Initial
Catalog=AdventureWorks_Data;Trusted_Connection=True; "
c.Open()
Dim da As New SqlDataAdapter("Select * from Store", c)
da.Fill(ds, "Store")
c.Close()

It crashes on open. The error message is:
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)

Where's the best place to start looking? (Remote connections?)
 
B

That remote connections comes forever if the Server is not to find.

..\SQLExpress is mostly the right name.
"Server=.\SQLExpress; DataBase=HKW; Integrated Security=SSPI"

I hope this helps,

Cor
 
I have rather hastily installed VB 2005 Express and SQL Server 2005 Express
on my machine and I haven't much experience with either. So far as I can
remember I have mostly used default settings. SQL Exp. is set to use Windows
validation. I already have SQL Server 2000 Developer installed so my SQL
2005 Exp. instance is called SQLEXPRESS. I'm not sure whether I've forgotten
how to code or I have a server problem.

As a test, I am trying a basic connect with ADO.Net and I am getting
nowhere. Here is the code.

c.ConnectionString = "Data Source=SQLEXPRESS;Initial
Catalog=AdventureWorks_Data;Trusted_Connection=True; "
c.Open()
Dim da As New SqlDataAdapter("Select * from Store", c)
da.Fill(ds, "Store")
c.Close()

It crashes on open. The error message is:
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)

Where's the best place to start looking? (Remote connections?)
Click to expand...

Good Morning,

Go to the Start menu on your machine and follow this path Start/Microsoft SQL
Server 2005/Configuration Tools/Surface Area Configuration

When you open Surface Area Configuration you will see form with the link
"Service Area Configuration for Services and Connections". Click the link and
you will see a tree on the left. Follow this path:
SOMETHING_SQLEXPRESS/Database Engine/Remote Connections.

Choose the option "Local and remote Connections", then beneath that choose the
option "Using both TCP/IP and Named Pipes".

That should fix the trouble you are having.

There is a second way to do this:

Go to the Start menu on your machine and follow this path Start/Microsoft SQL
Server 2005/Configuration Tools/SQLServer Configuration manager

In the manager, follow the path, Network Configuration/Protocols for
SOMETHING_SQLEXPRESS.

In the listview on the right, right click the Named Pipes and TCP/IP rows and
enable them.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
I've done what you recommended. I first went into 'Surface Area
Configuration' and set the option to 'Using Both TCP/IP and Named Pipes'. (I
confess that this is the first time I have been in this particular screen,
mainly because in my 20 years in programming, I have never heard the term
'Surface Area' used in conjuction with databases.)

I then rebooted just to play safe. No change. Same error message.

(Incidentally, given that all of this is happening on one isolated home
laptop, how can this be termed 'remote'?)

The Named Pipes and TCP/IP in the main configuration screen were enabled
already.
 
As Cor pointed out, the "Server" name or "Data Source" in your
ConnectionString is wrong. Since the SQL Server Express is an named
instance, it should be in this format "ComputerName\InstanceName" or
".\InstanceName", this is, in youy case, "YourComputerName\SQLEXPRESS" or
".\SQLEXPRESS".

Assume the SQLExpress set up is OK, whther you can actually access the SQL
Server Express will depend on which user account you application is running
under. After installing SQL Server Express, if you did not add any SQL login
in the SQL Server Express, only local admin user account can connect to it.
SO, if your application is ASP.NET, you must add an SQL login that maps to
the user account rinning ASP.NET app (ASPNET/Network Service account, be
default).
 
B.,

I always have to use a Data Source like this for local connections:

Data Source=(local)\SQLEXPRESS

Kerry Moorman
 
Open SQL Configuration.

1. Add protocols other than local (like named pipes, for example)
2. (optional locally) turn on SQL browser

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************
 
I've modified my connection string to use (local)\SQLEXPRESS (and .\SQLEXPRESS)

The problem now seems to be one of security. I'm getting login failed
messages.
'Cannot open database "AdventureWorks_Data" requested by the login. The
login failed.
Login failed for user '<machine name>\<my account name>'.'

I am the only account on this machine and I have added it to the login list
in SQL Server manager and assigned it the sysadmin role.

Any suggestions?
 
1) Since SQL Server Express is installed on the local system, no other
protocols are needed -- shared memory should be best and you don't need to
turn on the browser or enable any ports.
2) Accessing the local SQLEXPRESS instance should be clear by now...
"SERVER=.\SQLEXPRESS"
3) Accessing the selected database (on this instance) might be an issue if
the database (adventureworks) does not know who you are. To deal with this
issue, you either need to log on with a Windows domain account that's in the
Administrators group or create a Login on SQL Server for your windows
account. I assume you aren't using Mixed Mode security. These accounts can
be setup with SQL Server Management Studio Express (downloadable). Just make
sure your Windows domain account is granted sufficient rights to the target
DBMS.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
 
I'm getting more confused. I was finally able to connect with AdventureWorks.
The whole point of my post was to get my code to the point where I could run
the chapter 3 example. I have translated into vb since I am not familiar
with C#.

Using the same style connection string that I used to connect with
AdventureWorks ("Data Source=.\SQLEXPRESS;Initial
Catalog=TK431Chapter8;Trusted_Connection=True; ") causes a crash:

[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified

If I add the provider declaration the string becomes:"Provider=sqloledb;Data
Source=.\SQLEXPRESS;Initial Catalog=TK431Chapter8;Trusted_Connection=True; "

and I get the exception: Login failed for user '<user name>'. The user is
not associated with a trusted SQL Server connection.

This database is on the same server instance as AdventureWorks.

(Incidentally I am running Windows XP Pro and my Windows login id has
computer administrator authority.)
 
Ok, let's take a deep breath.
1) When connecting to SQL Server one should use the SqlClient .NET Data
Provider. I'm sorry, but I assumed that you were doing so. This means you
should have code that instantiates a SqlConnection object and sets the
ConnectionString property of that object. Your first ConnectionString (shown
below) seems correct.
2) This ConnectionString will not be correct for the Odbc .NET data provider
or the Oledb .NET data provider. I do not encourage anyone to use these to
access SQL Server.
Let's see the code you're using. Be sure to check out
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html
which details these and other issues.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
-----------------------------------------------------------------------------------------------------------------------


B. Chernick said:
I'm getting more confused. I was finally able to connect with
AdventureWorks.
The whole point of my post was to get my code to the point where I could
run
the chapter 3 example. I have translated into vb since I am not familiar
with C#.

Using the same style connection string that I used to connect with
AdventureWorks ("Data Source=.\SQLEXPRESS;Initial
Catalog=TK431Chapter8;Trusted_Connection=True; ") causes a crash:

[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified

If I add the provider declaration the string
becomes:"Provider=sqloledb;Data
Source=.\SQLEXPRESS;Initial Catalog=TK431Chapter8;Trusted_Connection=True;
"

and I get the exception: Login failed for user '<user name>'. The user is
not associated with a trusted SQL Server connection.

This database is on the same server instance as AdventureWorks.

(Incidentally I am running Windows XP Pro and my Windows login id has
computer administrator authority.)

William (Bill) Vaughn said:
1) Since SQL Server Express is installed on the local system, no other
protocols are needed -- shared memory should be best and you don't need
to
turn on the browser or enable any ports.
2) Accessing the local SQLEXPRESS instance should be clear by now...
"SERVER=.\SQLEXPRESS"
3) Accessing the selected database (on this instance) might be an issue
if
the database (adventureworks) does not know who you are. To deal with
this
issue, you either need to log on with a Windows domain account that's in
the
Administrators group or create a Login on SQL Server for your windows
account. I assume you aren't using Mixed Mode security. These accounts
can
be setup with SQL Server Management Studio Express (downloadable). Just
make
sure your Windows domain account is granted sufficient rights to the
target
DBMS.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
Click to expand...
Click to expand...
 
Back
Top