dataAdapter.Update / SQL PasswordHash NULL problem 1

[thedougster]

Some of you may have seen my earlier thread “PasswordHash NULL problem”. I’ve started a new thread because investigation has shown that the problem is actually quite different than I previously stated. Also please note that this is unrelated to another of my previous threads, “dataAdapter.Update problem”, which incidentally has been resolved.

I’m learning SQL. I’m accessing database SQL2008 AdventureWorks, table Person.Contact, which has a column PasswordHash, of type varchar (128). Here’s the code I’m using to save records:

// here dataTable and dataSet have been declared at the class-wide level as
// private DataTable dataTable;
// private DataSet dataSet;

DataRow row = dataTable.Rows [currRec];

row.BeginEdit ();
row ["Title"]        = txtTitle.Text;
row ["FirstName"]    = txtFirstName.Text;
row ["MiddleName"]   = txtMiddleName.Text;
row ["LastName"]     = txtLastName.Text;
row ["Suffix"]       = txtSuffix.Text;
row ["Phone"]        = txtPhone.Text;
row ["EmailAddress"] = txtEmailAddress.Text;
row.EndEdit ();

try { dataAdapter.Update (dataSet, "Person.Contact"); }
catch (System.Runtime.InteropServices.ExternalException exc)
{
    MessageBox.Show (exc.Message + "\n\n" + currRec + "\n\"" + 
        dataSet.Tables ["Person.Contact"].Rows [currRec] ["PasswordHash"].ToString () + "\"", 
        "System.Runtime.InteropServices.ExternalException");
}
catch (Exception exc) { MessageBox.Show (exc.Message, "Exception"); }
            
try { dataSet.AcceptChanges (); }
catch (Exception exc) { MessageBox.Show (exc.Message, "dataSet.AcceptChanges ();"); }

When I edit and save an existing record (which already has a PasswordHash) to the locally resident DataSet and then to the database, it works fine (which of course means that the AdventureWorks sample database is not read-only). But when I try to save a new (inserted) record, even if I include a statement

row ["PasswordHash"] = "GylyRwiKnyNPKbC1r4FSqA5YN9shIgsNik5ADyqStZc=";

in the above Edit, I get the following System.Runtime.InteropServices.ExternalException message:

Cannot insert the value NULL into column 'PasswordHash', table 'AdventureWorks.Person.Contact'; column does not allow nulls.
INSERT fails.
The statement has been terminated.

I get this message despite the fact that the PasswordHash is displayed in the MessageBox as being in the Dataset, and the statement

dataSet.AcceptChanges ();

throws no exception!

Using SQL Server 2008 Management Studio Express, I can find no property of column PasswordHash that would account for this.

Can anybody tell me: how can I write to the PasswordHash column? (This is the most important question in this post, and what distinquishes it from my previous thread.)

Next question (for when I have the above issue resolved): although "GylyRwiKnyNPKbC1r4FSqA5YN9shIgsNik5ADyqStZc=" is a real password hash that I lifted from an already existing record (see above), I’m pretty sure I can’t just plunk it into a new record, because I notice that every record has a different password hash, which means every record has a different password. This seems very strange to me. What purpose could this serve? And more importantly: how can I generate valid password hashes for new records that have not (yet) had passwords associated with them?

Or perhaps I can make SQL Server Management Studio allow NULL in the PasswordHash column. I discovered this would be possible in SQL Server Management Studio via:

expand table | expand Columns | right-click PasswordHash column | click Modify | in lower right frame: toggle Allow Nulls from No to Yes

On doing so and then attempting to exit SQL Server Management Studio, I got a dialog box saying:

Save changes to the following items?
SQL Server Objects
<Server name>.AdventureWorks - Person.Contact

Clicking Yes elicited the following message:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Contact (Person)

SQL Server Management Studio's onboard Help says I can override the "Prevent saving changes that require the table to be re-created" setting via:

Tools | Options | Designers | Table and Database Designers | Prevent saving changes that require table re-creation

I can try this, but I wonder if it might be dangerous, under two possible scenarios:

Firstly, if for whatever reason the table can't be re-created, could I possibly destroy the original table in the process and then have to reinstall the AdventureWorks database? I don't want to have to do that, since for some unknown reason I had a very difficult time installing it the first time.

And secondly, I have received the following warning about allowing null password hashes:

These tables are usually related to one another. There are referential and domain integrity checks within the tables across the database. Although you might be able to change some of these, some of the relationships and checks might be broken because of your update.

In other words, the writer speculates that allowing null password hashes might compromise the relationships between tables. For that matter, I suppose the same might be said about using “fake” password hashes, as described above.

On the other hand, wouldn't a password hash be relevant only to applications that use passwords? All I want MY application to do is to edit, insert and delete records. It won't require any passwords. Is it really likely that relationships between tables could be compromised if null or “fake” password hashes are used?

For what it's worth, I'm working in a 32-bit environment with the following software:

SQL Server 2008 Express with Advanced Services
database: SQL2008 AdventureWorks (schema.table: Person.Contact)
SQL Server 2008 Management Studio Express
Visual C# 2008 Express

 

[mrdenny]

Disabling the setting which prevents SSMS from making changes which cause the table to be dropped and recreated is no problem. That setting is there so that people with 100 Gig tables don't make changes they don't understand in production. If you are working with adventure works it's no problem.

What happens if you try to manually enter a new row into the table? It doesn't matter what password hash you provide as long as it is a valid string.

I don't have a copy of the AdventureWorks database handy so I can't look at the table structure but it should allow you to simply put the value in there.

The PasswordHash columns requires a value so that you can't setup a login to the AdventureWorks application that doesn't have a password. It's simply a business rule being enforced via the database.

Use the HASHBYTES code that I gave you in the other thread to generate a hash of your own value to use as the password hash value.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[thedougster]

mrdenny:

Disabling the setting which prevents SSMS from making changes which cause the table to be dropped and recreated is no problem. That setting is there so that people with 100 Gig tables don't make changes they don't understand in production. If you are working with adventure works it's no problem.

Thanks, I'll try it.

What happens if you try to manually enter a new row into the table?

As I had said:

when I try to save a new (inserted) record, even if I include a statement

row ["PasswordHash"] = "GylyRwiKnyNPKbC1r4FSqA5YN9shIgsNik5ADyqStZc=";

in the above Edit, I get the following System.Runtime.InteropServices.ExternalException message:

Cannot insert the value NULL into column 'PasswordHash', table 'AdventureWorks.Person.Contact'; column does not allow nulls.
INSERT fails.
The statement has been terminated.

I get this message despite the fact that the PasswordHash is displayed in the MessageBox as being in the Dataset, and the statement

dataSet.AcceptChanges ();

throws no exception!

It doesn't matter what password hash you provide as long as it is a valid string.

As I had said:

...I notice that every record has a different password hash, which means every record has a different password. This seems very strange to me. What purpose could this serve?

I'm not saying I disagree with you that I can throw any valid string in there, I just don't understand what this column is really for.

I don't have a copy of the AdventureWorks database handy so I can't look at the table structure but it should allow you to simply put the value in there.

As I had said:

Using SQL Server 2008 Management Studio Express, I can find no property of column PasswordHash that would account for this.

So for whatever reason, it WON'T allow me to put ANY value in there.

Use the HASHBYTES code that I gave you in the other thread to generate a hash of your own value to use as the password hash value.

I will if it comes to that, but first I'll try recreating the table; it's the simplest solution.

Thanks for your help.

 

[thedougster]

mrdenny:

I disabled the setting which prevents SSMS from making changes. I actually had to do this for column PasswordSalt as well as column PasswordHash. It works! Thanks a lot.