Upgrade from V6 to v7.x with an MSSQL SERVER DATABASE

1st of all, backup your existing ASPProtect files and database before performing this upgrade. Please be really careful while performing this upgrade. Version 7.x  is a highly advanced application compared to any previous versions. CJWSoft under no circumstances is responsible if you lose information or have website downtime.

BOTTOM LINE: (PERFORM THIS UPGRADE AT YOUR OWN RISK)

To do this upgrade you're going to need to have SQL Enterprise Manager and SQL Query Analyzer

That being said, on with the upgrade..

Open your SQL database in SQL Enterprise Manager

Regarding the following Tables

Rename "Users" to "ASPP_Users"
Rename "Groups" to "ASPP_Groups"

If you do not have a "Groups" table do not worry about it right now.

Now right click and DELETE the Config Table.
Yes, delete it..

Now, go into Design View for the "ASPP_Users" Table.

Rename the "Password" field to "Old_Password"
Be sure to spell it perfectly using the Underscore

If you have a "Groups" Field... leave it alone
If you do not have a "Groups" Field add one and make it a "nvarchar" field with a lengh of 255.

Now, we are going to add a few more new fields.

Add a field called "Redirection_URL" make it a "nvarchar" field with a lengh of 150.
Add a field called "PayPal_Subscriber_ID" make it a "nvarchar" field with a lengh of 100.
Add a field called "Newsletter" and make it a "bit" field
Add a field called "Password" and make it a "nvarchar" field with a lengh of 100.

Now close that window and save the changes..

Download these scripts.
2005-02-23_171110_aspprotect_v7_sql_upgrade_scripts.zip

Now open up SQL Query Analyzer
Connect to your SQL server.

Then load the script  "aspprotect_v7_config_table.sql" into the Query Analyzer.  Click the green play button at the top. If everything goes well the response should read something like this.

******************************************************
(1 row(s) affected)
******************************************************

Now, if you had a Groups Table your done with the database changes.

If you didnt have a groups table.

Then load the script  "aspprotect_v7_groups_table.sql" into the Query Analyzer.  Click the green play button at the top.

Now, go back to your database in Enterprise Manager and make sure all 3 tables are there and look ok. You might need to do a refresh or two to see them.

Now make sure an existing or new SQL user has (public / datareader / datawriter) permissions for all three tables. You reference this user in the asp code connection string so this user must be set up correctly. You may need your SQL server admins or hosting company to help you on this step as you may not have access to do this. You may not need to create a user and set permissions as the sql user you were logged in as to use query analyzer may by default get the correct permissions on anything you create.

Regardless, as you can see from this screenshot I have a SQL user called "aspprotectuser" and proceeded to set the permissions for that user. Under database access giving him (public,datareader, and datewriter permissions).


Now, you are done upgrading your SQL Server Database.

The existing passwords still have to be encrytped and moved from the "Old_Password" to the "Password" field

To that we have a special page we run in the application that will take care of that.

So, for now... go install the application, but using the database we just created.
Follow these instructions for the most part...
http://support.cjwsoft.com/forum/forum_posts.asp?TID=181& ; ;PN=1

When you get to the part where you finally get into the admin area and need to make an admin account you will notice that your existing user database is there but none of the users have passwords if you look at them in the edit screen.

That is normal. Simply do what the instructions say and create and admin account using a username that does not exist..

Then log off... then back in as that new admin account. If that works you are ready to convert the passwords.

This part is very easy.

You want to run a special page via the browser.

http://www.mysite.com/password_admin/convert_to_encrypted.asp

Replacing the part in blue with your website info.

Once you get the page running you will see a login prompt and one form field just like before with the "get_me_in.asp" page

You will need to paste the "PasswordEncryptionKey" value that you used in the "config_inc.asp" file in the form field and hit enter.

If all goes well you will see a page telling you to click here to encrypt the passwords and copy them over.

So do what it says... dont click more than once and wait.

Eventually it should say it is finished. So go log in to the admin area of the system using the new admin account.

Now edit some users but dont save... you want to see if the passwords are showing up. If they are the conversion worked. If you see nothing or a garbled mess it did not work and you made a mistake during this whole process.

If things went well backup and delete the conversion file below.

http://www.mysite.com/password_admin/convert_to_encrypted.asp

You do not need it anymore.

Once you are positive every thing is running smoothly and everyone's passwords are ok you can go in and delete the "Old_Password" field as well.



If things didnt go well.. try again from scratch and go slowly.
If they still dont go well get ahold of us for help.

We are here to help, but if you really want us to we can be hired to do the conversion.

Hi,

First and foremost, a great product. I downloaded it last night and it took little effort to get it up and running

.....now a little question...

Has the software/code been tried out on a Mysql db and if so did it work?

The reason I ask is that my website is very busy (1.5million page views per month avg) and I'm considering converting the ASPBanner Access db to a Mysql one to help handle the traffic. For every page view one banner impression is being made which means that the Access db is under a bit of pressure

This afternoon there were 1012 simultanious users on the website and it froze with an error message displaying where the banner should have been (I didn't quite catch the message but something to do with the banner script timing out). I'm not sure if the Access db had something to do with it but it seemed too coincidental. Rebooting the server cured the problem but obviously kicked off the visistors as well
At present there's 668 online and no problems so I'll have to monitor it to see how it goes but if anyone can answer the Mysql question I'd be grateful

Thanks,

Dave

it has been 3 days since I logged myself in under Admin, and all the user information on the User Activiy screen seemed to be gone.

is there any specific length of period it refreshes its user information??

enjoy the bar.. drink one for me.. cause that was the problem..

Okay thanks, I'll have to consider wether or not to go down the path of the upgrade or cut my losses as the Access cannot handle the pressure

It's just errored out again with the following message:

msxml3.dll error '80072ee2'

The operation timed out

/forum/banner.asp, line 15

Regards,

Dave

Okay, here's the problem.  The out of the box sql script creates a catalog called aspphotogallery and a user id aspphotogalleryuser.

The out of the box GalleryConnectionString tries to access a catalog called aspgallery and a user id aspgallery

'*** GalleryConnectionString = "Provider=sqloledb;Data Source=p600laptop;Initial Catalog=aspgallery;User Id=aspgallery;Password=temp;"

I see the readme.txt has been corrected but the line in dataconn_inc.asp still needs to be changed.

I did a google search and it turns out that error very well might have to do with ASP trying to send an email and that process failing.

That tells me your emailing from the application is not working so it is probably not something you edited incorrectly.

see this article...

80040211
http://www.aspfaq.com/show.asp?id=2026

Emailing was working as when I did your installation (I think I did it months ago, didn't I) so it must be some incorrect changes to your email settings in the admin settings screen. Try sending an individual email to a user from the admin users screen and see if it works. My guess is you will get the same error and means your email sending options are no longer correct or valid.

If that is the case I would ask you if you changed them or possibly something changed as far as your email setup goes. Passwords ? EMail Server .. etc etc etc

I am having problems with a password a user wants to use.  He wants HANNAH.  When he (or I) try to log in with his username and password, I get a syntax error  -

Syntax error in string in query expression '(Username = 'changedforsecurity') AND (Password = ' éG'.
pathOnComputer../1protect/check_user_inc.asp, line 114

I've obviously changed the username and the server path in the info above for security. 

I have not messed with the encryption.   

What is it that the system doesn't like in the word HANNAH as a password?  I would just change his password for him but this guy has enough trouble just turning the computer on!  Confusing him with a new password would take weeks to set him straight. 

Thanks,
Mick

nope ,sql server has nothing to do with this

I am talking about the folder pictures are stored in.. it needs modify permissions set for the internet guest account like those articles talk about

where is your site ? I'll taka a look

Chris,

I have had a couple of users log out of the system as they are supposed to, move to another computer and not be allowed to login because of "they are currently logged in using another IP address" when in fact they logged off properly.  Is there anything that they should be doing differently?

Thanks,

Jess

Chris -

Sorry, I am looking at your code on view_item.asp and I have noticed that you are calling 3 variable; start_date, end_date, and image_url that do not exist in the databases that you provided.  Do I have the updated package for ASPVendor?

again.. more custom code work would be required to handle it

Thanks, I really appreciate you working on this. The software works GREAT and the support you give can't be beat. I will definately tell other that are looking for software for their sites to check your first.

one more question.

To use the bulk upload feature of aspuload, do I just need to add another upload section to the same upload page, that will allow more than one file to be selected then tell it to put the files in as Photo 1, 2, 3 etc.? I am a rookie at ASP but I think this is possible and with a little research I should be able to get it going.

Just want to make sure I am on the right track.

Thanks

thanks.. it was not taken as a complaint.
I just wanted to explain

When you said you tried using the web version of sql manager. Did you use the microsoft one I link to here "just curious"

http://support.cjwsoft.com/code/moreinfo127-1.htm

The code is ok..

Your getting an error because no banners are showing up in that zones rotation.

run this directly in the browser and you will see that there are no banners being generated for that zone ID
http://www.poconocommuter.com/aspbanner/aspbanner_inc.asp?Ba nnerZone=6

As a matter of fact I dont see any banners showing up for any zones previous to 6 which tells me either you dont have valid banners in there or the application variable info got corrupted do to a server crash / power off or something.

http://support.cjwsoft.com/code/moreinfo95-1.htm

It's not working because i guess im copying the viewstate also...and it comes up

Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that <machineKey> configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.

Hi,

I wanted to see if you had any suggestions for converting from Access to SQL sever database. I attempted this earlier today performing the following steps.

1) Create SQL Tables using Enterprise Manager / SQL Scripts
2) Use DTS to move all of the existing table data to the sql tables.
3) Update dataconn_inc.asp to use SQL and the required connection string.

When these steps were complete I was able to login to ASPProtect as an admin and search / find both groups and users. However, any attempt to edit or create users resulted in a "the page ... had a problem ... " type problem. It seems that I can read from the db fine but and getting errors writing to the tables. The user id that is being used to connect to the db is the [dbo].

Any additional hints for this procedure?

Thanks

Darrell

as far as sql goes if you follow the instructions with give for setting up a new database you shouldnt have any issues and permissions should be already set. because we handle that in the sql script we give you.. "its a good thing to look at and it is pretty easy to understand what is going on""

however using another account could cause permissions issues.."yes, even sa"     basically the username your using needs datareader and datawriter permissions to all tables used by the photo gallery system and you probably have to go specifically set them usin ght e security tab for your database in enterprise manager. This is more of SQL server 101 than anything to do with the Photo Gallery Code so I am not going to get into it too deeply, but that is definetly the issue.  Permissions...

That would be great.

I am sure you know that many virus that are sent via email have the same property. (double extension). The code can be executed even though Windows identifies them as simple text files etc.

Thanks again

Does emailing work under the simplest scenario ? (directly from the users screen)

Thats the way to test it..

All that error means is whatever reason the settings you have chosen are not working. It could be the server. It could be what you chosen. (and yes I realize your pop info from outlook should probably work with the settings you chose)

Whenever I do installs I often have to try 3-5 different emailing scenarios before I come across one that works.

Each time making some changes and sending out test emails from the users screen until I get somewhere. Often time getting a working example of how your Hosting Company wants you to send email from ASP is the info to get your hands on. (what method and settings)

In this case they may have blocked the usee of a remote server and want you to use some other settings for sending email from asp. A lot of times they put that info in their help system.

If I were you I would start by trying the other two CDOSYS options for starters, and then try the remote server option again but using "localhost" as the server, if none of those work consult your host for example code and settings to send email from asp. If you still have no luck I can help for sure.

Realize too when testing the emails may take a bit to arrive. A delay of sorts. Best to type in a quick note about which method you are trying in the email text. That way when you finally get one delivered you'll know which method worked.

After I approve someone's username and password, then go to send them the default e mail telling them of the approval, the e mail never goes through.  The e mailing used to work well for many months, but it stopped working.  Any thoughts?

Spider

I downloaded v7 3/7/2005

I entered a password that was supposed to be all caps with only first letter caps. 

it is odd, if I go to other user and enter wrong password that does not come up.  it apprpriately goes to a screen that says Access Denied.

thx

here is a thread that may help you if this is what you were getting at

http://support.cjwsoft.com/code/moreinfo389-1.htm

I've got an ecommerce module that's running on it that uses access for the db.  Connecting into that thing is slow as well, but I figured that's because of the complications and volume it handles.

So as far as importing on a faster machine and copying it over to the server...what suggestions do you have?

Really, only you or your hosting company would know that information...

It is usally installed in the "_database" folder but the physical path including the drive letter  to that can only be known by asking your hosting company or by using server mappath to learn the path on you own.
http://www.powerasp.com/content/hintstips/physical-path.asp

That folder also needs special permissions. The permissions it needs are covered in the documentation for the software and generally only your hosting compnay can set those permissions.

The online support forums here are full of information and resources on correctly setting up data connections. It is ASP 101 and something you really need to have an understanding of.

I am happy to answer questions and try to help, but if you dont want to deal with it or can't we do offer installation services.

http://www.cjwsoft.com/installation_service.asp

Access to some sections of the forums must be requested.
Please Click on the following link and read all of it carefully.

http://support.cjwsoft.com/