Progress != Perfection

The new project does a lot of good things. Unfortunately, it didn't make it so that my website could connect to the database. 

Google lead me to a page where a person asked the exact right question (Praise Google!). As I suspected, I had set up my database to use Windows authentication. That means that the tools could access the database as tqwhite, but the website could not. It runs as IUSR (I think) and that has no way of logging in so that MSSQL can inherit its privileges.
Turns out that one can use the Manager to change the security mode to allow both Windows authentication and the usual SQL user model. Reading further, I found that MSSQL ships with a user "sa" that is disabled by choosing Windows authentication. Blah blah.
Bottom line, I the thread I was reading also told me about another page [see update below] that explains how to deal with this. Basically, it's right-click on the database in the Manager and change Security. Then restart the server and execute these two queries:
 
ALTER LOGIN sa ENABLE
ALTER LOGIN sa WITH PASSWORD = 'VeryStrongPasswordHere'

Obviously, you should choose your own password.
But then, I need to make it so that the website knows about these credentials. I right-clicked my butt off on the database connection and couldn't do it. Eventually, I chose properties and found that the connection string was:
 
Data Source=QUINDOWS;Initial Catalog=tmpTest;Integrated Security=True

I tried to edit this but it wouldn't let me. I'm sure I'll figure it out, but instead I edited the generated code file (temporarily). In my sandbox it's App_Code/DataClasses.designer.cs. I found the code that accesses a static class to get the string and changed that part to:
 
Server= QUINDOWS;Database= tmpTest;integrated security=sspi;Uid=sa;Pwd= VeryStrongPasswordHere;Trusted_Connection=no

Save, Run, Scream Eureka!!
 
I got a data grid.
 
Obviously, the next step is to figure out how to implement the new authentication using the tool, but at least I know that it will work when I find it.
 
UPDATE: I'm not sure if I'm stupid or some context sensitive thing changed. However, on the Server Explorer where the database connection resides, I was able to right-click and select Modify Connection. I changed it to SQL authentication and entered the info. I undid the damage I had done by editing the generated code (had to delete and recreate). It works.

UPDATE 6/29/10: As you might have found out, the page that explained how to set the security mode has become password protected. I found new info HERE. It says to open SQL Server Management Studio, right-click on the server item at the top of the object list at the left. Then, choose Properties, then Security. You will see a section on Server authentication. Choose SQL Server and Windows Authentication Mode. Life will be good. You can then add the users using SQL commands as above.

Changing Project Types

Still trying to get Linq to SQL going, I got things working enough to learn that the namespace created for my database classes wasn't being recognized. One way to figure out what the system knows about is to look at its auto-completion proposals. When I typed, "WebApplication1.", it offered me "default" and "demos". The latter is a folder I created and have been able to use. Upon closer inspection, I saw that the icon for demos was yellow and for App_Code wasn't. I can't figure out a way to change it.

However, and this is the point, during this adventure, I have found that Visual Studio can create two different kinds of web projects, "web site" and "web application". When I started, I thought "Hell, I'm a developer. I write applications," and chose application. During the learning so far, I found out that Web Application is not exactly deprecated, but it's not the cool kind of project, Web Site is. Application was added after VS 2005 was introduced because the apps developed in 2003 wouldn't port forward. They had project build files or something that are not part of the new, improved Visual Studio.

This combined with the fact that some of the behind the scenes wiring in my sandbox was not working made me decide to blow off the Application route. I killed it all and create a new Web Site project. After going through the rigamarole to add the database, gridview, etc, I get a clean compile. The namespace for App_Code is recognized and, for good measure, I didn't have to manually add the Using statement to include Linq. This is progress.

Hello World: Linq to SQL

I used MSSQL Manager to create a table and put some data in it. I used Visual Studio to view that data. Big stuff. The connections are developing.

I learned about 'relationships'. This is something my old friend mysql does not have. Relationships enforce foreign keys. I made a pair of tables, item and category. Item had a column named category_id. When I made a relationship (using the diagram tool is pretty cool), I could not create an item with a bad category_id and I could not delete a row from categories for which an item existed. Seems like a good thing.

I am interested in heading into the Object Relation Mapper (ORM!) direction. As best I can tell, I will eventually use nHibernate. By all accounts, it's a rich, mature tool. However, .NET comes with something called Linq. It does a lightweight version of ORM (or so I've read) and I figure that it's the place to start on this.

My demo project is to create a small page that lists items, first based on a category that is hard-coded, later based on a select menu of some sort. I figure that this will allow me to experiment with Linq ORM and connecting it to the page in both directions.

This Wrox book I have* has a chapter on using Linq, bless their hearts. The basic idea is that I add a gridview object to my page, add a database object to the demo site using the Visual Studio (App_Data, new item, Linq to SQL, graphic tool to drag database tables to autocreate orm classes, cool stuff) and, put the database to gridview connection into the page_load event. Done. Try it out...

Big surprise, it doesn't work. Bigger surprise, it's not apparently my error, to wit:

Compiler Error Message: CS0234: The type or namespace name 'Linq' does not exist in the namespace 'System.Data' (are you missing an assembly reference?)

I google and find a page that says that I need to add a configuration element to my web.config file:

<add assembly="System.Data.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

This gets rid of the error message. Unfortunately, it doesn't give me data. Onward!

*Beginning ASP.NET 3.5 in C# and VB, ISBN: 978-0-470-18759-3 (Amazon)

MS SQL is on the Air!!

The error message I finished last week with told me that the name pipes weren't working. That was still the case this week. It's irritating because I did the vanilla installation of everything and accepted the defaults. Honestly, I think that should guarantee success.

However, I have won this battle. I started reading the help to learn about this pipes business. It turns out that, by default, MSSQL listens on the pipe named
\\.\pipe\sql\query

unless you change it. But, the configuration program named the pipe

\\.\pipe\MSSQL$SQLEXPRESS\sql\query

Why? Because it's dumb.

Also, I read about connection strings and, of course, they include the pipe name unless it's default. I figured that Visual Studio, since I have not told it anything about pipe names, would be trying to connect on the default.

Solution: I renamed the pipe to be the default and, bingo, it works in Visual Studio.

Now, what can I do with that connection? That battle is engaged.

UPDATE, 6/27/10: Doing this requires accessing:

Start->Microsoft SQL Server->Configuration TOols->SQL Server Configuration Manager

Then, in the left column click on

SQL Server 2005 Network Configuration->Protocols for MSSQLSERVER->properties

This will open a list of protocols in the right pane.

Double click on Named Pipes Properties and you will see a place to enter the Pipe Name (and enable it).

That's easy, right? I hate these people, even all these months later.

Hello World: MS SQL Server - NOT!

One of the things I probably should have mentioned is that I am a fan of books. It's an expensive hobby, especially for technical stuff. I tend to get the book, use it for about two weeks. After that, it's only role is to keep my bookshelf from floating away. Still, I find it helpful to have some ideas and instructions to inspire me. Having pages to fold, write upon and stick postits on is also helpful. Presently, I am using Beginning ASP.NET 3.5 in C# and VB by Wrox. It seems to be covering my learning experience perfectly well though with a little more introductory info that I need. (I am already pretty good at both html and sql.) That said, it's got me moving along.

Anyway, my goal today is to activate SQL Server and touch it from both Visual Studio and a web page. I'm an ambitious sort.

So, I have browsed my book and found that Visual Studio will create a database for me. Cool beans.
I start out by looking at the configuration manager. It's got a bunch of stuff I don't know what it is. I look at my book and it suggests SQL Server Management Studio Express. Cool. I go get it, install, and find that it has all kinds of stuff I don't about, too. Another tool available, though.

After some browsing and thinking, I discover that VS (I can only type Visual Studio this one more time) has a thing in it's view menu called Server Explorer. I'm happy to say that it seems to see my account. There is an item called Data Connections. A right-click yields a menu that offers to Connect or, woo hoo!, Create a New Sql Server Database. It's easy to use and obvious as well, so I type type type and click go. It tells me that it can't connect to the database. I remember seeing somewhere that you need to turn on more access methods to use it from the web.

My book explains that I need to use the Configuration Manager to turn on pipes and tcp. I do it and am confronted with the (perfectly reasonable) message that these won't take affect until the database server is restarted. A little google lets me know that the Management Studio is the tool for that. I hit my server, right-click and select restart. Access Denied!!

I groan thinking of the trauma I had the other day with a similar message and IUSR. Google is my friend, though. The answer is that I need to run the program as an administrator. I have not clue how to do that but, google tells me that I can run anything as an administrator by right-clicking and choosing, get this: Run as Administrator. It's pretty much like sudo. If it needs to, it will ask for an admin password, and then run the program with uber-privilege. Done and Done. The server restarts.

I head back to VS and try it out. I'm thinking, Oh Goodie! No dice. Still gives me the same error that comes down to the fact that it can't locate my sql server. I head back to the manager program to see if it will do anything other than restart and, yes, it will create a table and looks good.

But, now it's time to get out of here because I can't stand it any more. I cannot for the life of me figure why a set of programs and services that are designed to work together don't. It's not like it's some sort of compromised environment. It's a fresh install of everything. As far as MSSQL goes, it's totally vanilla. I wish I could say it's a billion times easier on Linux or Mac, but it's not. However, they at least have the excuse that 1) nobody charges for LAMP, and 2) they are not written by a single company and sold as a turnkey solution. Arrrrgh.

Still, it's fun.

Peace.

The Joy of Learning

It's no secret to people that know me that I despise Microsoft. I've often said that I would vote Republican before I would use Microsoft. Neither are on the horizon. However, this targeted incursion into MS territory is really joyful. I still dislike just about everything I have seen about Windows. Except for the fact that Visual Studio runs in it, I would never touch the stuff. I don't feel so much about .NET. It's got some really clunky things going on. There is a lightness to php (!!) that is sorely missed. .NET mediates everything between me and the web so I end up feeling a little like I'm losing my identity as a programmer. Forget about code strategies, just use learn the damn Server Controls!

Realistically though, it's just a different style of programing language. I used Cake PHP for a recent job and it had php objects that want to stand between me and my web pages, too. The people that specified Cake for the project rightly felt that productivity, uniformity and portability would be increased with Cake. Those are all things that try to steal your identity as a programmer, too. Of course, they also free you to focus on other things.

All is to say, my descent into .NET is really just what it claims to be, a new programming language and environment. Like any learning experience, there are things that are no fun to learn. However, one of the things that is thrilling me these days is the sheer brain exercise of learning. A guy whose blog I read with deeply mixed feelings, Dave Winer, switched from his long-time Mac use to Windows in a fit of pique a few years ago (he's since switched back). He wrote that he was thrilled by the exercise of new neuron paths. I'm with him all the way.

I love the large body of unknown information that I am required/empowered to master. The experimentation documented the other day and the one I'm embarking on this afternoon or Monday (connect to MS SQL) are just plain fun. And there are a billion more. And, once those are done, I get to do even more because, for me, at least, that's what programming is all about. I look at where I am, decide what is missing (or ask the BA) and then find a way to make it better. It's exploration and experimentation and, eventually, polishing and putting a bow on something cool.

Go out and learn something. It's worth it.

Recap of getting a virtual host equivalent (host header) working on Windows 7 with IIS7

1) Make a website. I think you can use something as simple as a folder with Default.aspx (containing regular html). Maybe there are other file types. That will be tomorrow's challenge.

2) Add IUSR to its permissions with Properties->Security->edit->add->enter object name->full permissions. UPDATE: it's a week or so later. I just added a new local host to my computer. It wouldn't work until I also added IIS_IUSRS. Apparently both are needed.

3) Go to IIS7 manager (control panel->administrative tools->IIS), click the outline in the left column until you see Sites. Right-click->Properties->add website. Enter things like I showed in the screen shot of the first post.

4) Edit C:\\system32\drivers\etc\hosts with Notepad in admin mode. Add a line like "127.0.0.1 www.sandbox.local" and save. It turns out that this is effective immediately.

5) Try the site.

6) Scream Eureka and make a cocktail. Accomplishment has been achieved.

Granting permissions for IIS7

I know that permissions are done in Windows through Properties (the equivalent of Macintosh 'Get Info'), so I used that. It offers a Securites tab and that shows a bunch of user type things and their permissions. None of them are say IIS_IUSRS.

I clicked Edit. Then I clicked Add. Seems easy but, what do I do then? Eventually, I pasted the value into the 'Enter Object Names' (a permission object?) and clicked a button called Check Name. It revised the string to include the server name. Eureka! It recognized it and did something sensible. Save save save. Try the website. Nope. But, it was Nope with a different error message. That spells progress.

The error message was now a 403.1 talking about access control, etc. I googled it ("windows 7" directory or page because of the access control list (ACL) configuration or encryption settings) and found a page that instructed me on how to set the IIS user. It basically comes down to finding that Authentication icon in the IIS7 manager, finding the site (sandbox) and checking out its Properties (right-click Properties is used for everything!). That page suggested I change the user to match my permissions but, seeing the user that was automatically set, IUSR, I thought, I'd add that to the permissions for Visual Studio's directory.

And that, my friends, did the job.

Trying to get websites to work on IIS7

See? We have another search friendly term. Turns out that google responds nicely to "IIS7". Also, I have found much better results when I quote "Windows 7" for google. It causes the search engine to treat that as a single word instead of giving me every page that has the word 'windows' in it. Enjoy.

The permissions thing I thought about last night is not the answer. The binding for localhost has the exact same permissions issues and it works just fine. I guess I'd rather this was the problem but, what the heck, at least it's another issue off the list.

Next, I am trying to figure out how to debug IIS7 in general. I looked at the places it appears to offer log files. Not much there.

Then, I got the idea to see if I could ping the site name (www.sandbox.local) from my command line. Localhost works. Sandbox, not so much. It "could not find host". Looking around, google showed me a guy named Mike Volodarsky. Check him out. He has some good-looking info on his site. Among those is a page specific to IIS7 server not found. Reading it, it became clear that my problem resembles a DNS failure. In localhost terms, that means that Windows 7 has the equivalent of the unix hosts file. 

In fact, it has exactly that. I dug through and found a wikipedia page that talks all about hosts files. For Windows 7 (and most other modern MS operating systems), the hosts file is at C:\system32\drivers\etc\hosts. Off I go but, notepad won't see it. Arrrgh! It's hidden. Open. Can't save. Arrrgh! Since I am running underneath of OSX and not serving outside my environment, I just changed the permissions to let any authorized user (aka, me) have full priveleges. Voila!

I entered:

127.0.0.1    *.sandbox.local

Save. Great hopes. Nope. Ping still says no such host. Have I said, Arrrgh! lately?

I tried restarting IIS7 by selecting my new entry in the manager and clicking restart. No dice. This makes no sense.

Obviously, time for reboot.

And ping works. And firefox shows me the next layer of this stinky onion.

"Cannot read configuration file due to insufficient permissions" from IIS Web Core.

Along the way, I found that there is an 'Authentication' icon that offers, among other things, "Anonymous Authentication". This is enabled. Not the problem, but good to know about.

I found somebody who has exactly this problem, including the fact that he's trying to run his website out of his Documents folder. He says, "To fix this problem, we need to grant the read permission to IIS_IUSRS on ‘httdocs’ folder." Ok. How?

Apache Virtual Hosts == IIS Host Headers

I learned this fact, after substantial research, from Microsoft itself.

So, I am trying to make this work. First, I fired up Visual Studio and created a new project, Hello World!! I tell it to show me the site, it calls the web browser with a long port number, ie, localhost:1234859. It works. Life is good.

I head over the IIS control panel, following the instructions. The basic premise is that you find an item called Sites, tell it to add a new one, and enter some info. I take a look at the default website and figure out what folder to refer to. After a couple of tries, I have an item that looks mighty good.

I try it and get "Firefox can't find the server at www.sandbox.local."

Thinking that there might be something wrong with my site, I revise the entry to point to inetpub/wwwroot.  No dice. Lots of other random stuff. No dice.

There is something called "Pass Through Authentication" which has a 'Test" button on the configurator. It doesn't pass. I think that, tomorrow, I will investigate this.