Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Difference btwn SQL Server and Express?

Author  Topic 

chuckles
Starting Member

9 Posts
Posted - 2010-05-05 : 16:06:02
Hi All,
First time poster here...

I'm building a web application on my local PC running XP, where I have installed SQL Server 2005. I'm able to connect to my database using this C#.NET code:


String strConnectionString = "SERVER=localhost;DATABASE=american;Connect Timeout=30;Integrated Security=SSPI";
String strSqlCommand = "SELECT * FROM Users WHERE email='" + UserEmail.Value + "'";
SqlConnection cnn = new SqlConnection(strConnectionString);
SqlCommand myCommand = new SqlCommand(strSqlCommand, cnn);
cnn.Open();


My admin set up a production server, but is using SQL Server 2005 Express, at least until I am convinced that Express will not have enough capacity to hold the application's data. Using the same code files, I am getting the error:
quote:

System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.


I've been looking around and there is a lot about this error message, but I'm not clear on whether they are answering things relevant to my case. I think I need to set up something different in Express, but I'm not sure what.

Yes, the same "american" database is set up on the production server.

Would greatly appreciate help with this!


Thanks,
Chuck

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-05-05 : 16:15:31
The edition of SQL Server is irrelevant to the error. Please see my blog post regarding it: http://weblogs.sqlteam.com/tarad/archive/2008/05/23/60609.aspx

You likely just need to create an alias on your client machine or specify servername,portnumber to connect to the server.

There is also a possibility that a firewall is blocking you. What OS is the SQL Server Express machine using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

chuckles
Starting Member

9 Posts
Posted - 2010-05-05 : 16:20:51
Hi Tara,
The production machine is running Windows Server 2003 R2. I think the SQL Server Express is on he same machine, so I didn't think it would be a firewall issue. I'm reading through your article now...


Thanks,
Chuck
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-05-05 : 16:23:17
From memory, SQL Server Express installs a named instance, the instance name being SQLEXPRESS.

Try using "SERVER=localhost\SQLEXPRESS;" in your connection string.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.
Go to Top of Page

chuckles
Starting Member

9 Posts
Posted - 2010-05-05 : 16:36:40
quote:
Originally posted by DBA in the making

From memory, SQL Server Express installs a named instance, the instance name being SQLEXPRESS.

Try using "SERVER=localhost\SQLEXPRESS;" in your connection string.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.


Hi DBA,
I tried this (with double backslashes to escape it), with no luck. Same error message.


Thanks,
Chuck
Go to Top of Page

chuckles
Starting Member

9 Posts
Posted - 2010-05-05 : 16:48:16
The plot thinkens...

I saw that my admin set up the SQLServer Express with a Server Name of REMOTE\SQLEXPRESS. When I substitute that in for localhost\SQLEXPRESS, I get the error:

quote:

System.Data.SqlClient.SqlException: Cannot open database "american" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.


Does this tell me that SQLServer is not set up on the same machine as my web server?



Thanks,
Chuck
Go to Top of Page

chuckles
Starting Member

9 Posts
Posted - 2010-05-05 : 17:13:46
I just figured out that Named Pipes, TCP/IP and VIA were all disabled and I changed that, but no luck, still the same "Login Failed" error...


Thanks,
Chuck
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-05-05 : 17:27:52
quote:
Originally posted by chuckles

The plot thinkens...

I saw that my admin set up the SQLServer Express with a Server Name of REMOTE\SQLEXPRESS. When I substitute that in for localhost\SQLEXPRESS, I get the error:

quote:

System.Data.SqlClient.SqlException: Cannot open database "american" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.


Does this tell me that SQLServer is not set up on the same machine as my web server?

It looks like your website is running under the 'NT AUTHORITY\NETWORK SERVICE' user context, which isn't setup to access the new server. You should probably run it under a domain user context, and set that user context up as a SQL user.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.
Go to Top of Page

chuckles
Starting Member

9 Posts
Posted - 2010-05-05 : 17:33:32
quote:

Does this tell me that SQLServer is not set up on the same machine as my web server?
It looks like your website is running under the 'NT AUTHORITY\NETWORK SERVICE' user context, which isn't setup to access the new server. You should probably run it under a domain user context, and set that user context up as a SQL user.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.


Great, but I'm not up to speed with how to go about this. What tools will I use to set all of this up? IIS?


Thanks,
Chuck
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-05-06 : 04:06:28
Have a look at this:
http://support.microsoft.com/kb/306158

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.
Go to Top of Page

chuckles
Starting Member

9 Posts
Posted - 2010-05-06 : 11:07:26
quote:
Originally posted by DBA in the making

Have a look at this:
http://support.microsoft.com/kb/306158

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.


Hi Again,
OK, I looked at the article and decided I needed to impersonate for all transactions. I added in the 
<identity impersonate="true" />
directive to the web.config file. It gave me an error, getting me to add an account REMOTE\IUSR_REMOTE account. Then some errors about not having SELECT permission, so I turned on a bunch of permissions using IIS.

Now, I'm back to the error:


System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

Source Error: 


Line 24: 				Server.HtmlEncode(User.Identity.Name) + "'", mycn);
Line 25: 			DataSet ds = new DataSet();
Line 26: 			myda.Fill (ds, "Users");
Line 27: 			DataTable tblUsers;
Line 28: 			tblUsers = ds.Tables["Users"];



In this case, it's pointing to the 
myda.Fill (ds, "Users");
line, which works fine in my local environment. This is in a much different place than before. What I now find confusing is how I was able to connect OK, but fail here, with this kind of error. I'll go back and look at Tara's article and see if that leads me anywhere, but further comments would be appreciated!


Thanks,
Chuck
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-05-06 : 11:11:37
Does the user you're impersonating have read permissions on the Users table?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.
Go to Top of Page

chuckles
Starting Member

9 Posts
Posted - 2010-05-06 : 11:22:41
quote:
Originally posted by DBA in the making

Does the user you're impersonating have read permissions on the Users table?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.


Yes, it does now. But that wasn't the problem. I saw that the code it was giving me wasn't from my logon page, it was from my Default.aspx page, which is where I was getting forwarded to. After changing the conection string on that page, it now works! Psych!

Looks like I want to place my connection string into the machine.config file, taking care of all my pages at once.


Thanks,
Chuck
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-05-06 : 11:24:41
machine.config is used for machine wide configurations. Unless you'll need to reuse these settings in other applications, then you probably shouldn't put them there.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.
Go to Top of Page

chuckles
Starting Member

9 Posts
Posted - 2010-05-06 : 11:32:01
quote:
Originally posted by DBA in the making

machine.config is used for machine wide configurations. Unless you'll need to reuse these settings in other applications, then you probably shouldn't put them there.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.


Fair enuf'. I was looking at the text on [url]http://weblogs.sqlteam.com/tarad/archive/2008/05/23/60609.aspx[/url]. Should I instead be putting this into my web.config file?

Thanks very much for your time and help!!!

Thanks,
Chuck
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-05-06 : 12:53:10
quote:
Originally posted by chuckles
Fair enuf'. I was looking at the text on [url]http://weblogs.sqlteam.com/tarad/archive/2008/05/23/60609.aspx[/url]. Should I instead be putting this into my web.config file?

I would think so. I'm not really an expert in securing .net web applications. Keep in mind that this file will contain the login details for the SQL account, so it will need to be secured.
quote:
Thanks very much for your time and help!!!

No worries. You might get more help if you asked this in an ASP.NET forum.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site