I Love Javascript!

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:

 

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.