Problem importing and exporting password column

Maxxim · November 12th, 2006, 02:50 PM

Hello!

I have to change my hosting server.

So I export all my tables using xml file

But on new server the passwords don't work!

To test other process I Went to my old query analyzer and I ran:

Code:

Select UserID, Password From Accounts_Users

Then I went to new server and ran:

Code:

UPDATE Accounts_Users SET PASSWORD = 0xD62F739DA2796769AF9E727E7905F91A369F44A WHERE UserID = 1

but curiously when I see what password the query placed in the table, the password don't match :(

Code:

SELECT Password from accounts_users where userid = 1

-- Returns:

0x0D62F739DA2796769AF9E727E7905F91A369F44A

In Some Way it appeared one more "0"
How can I solve this problem?

My table have more than 200 users :(

Thanks

englere · November 12th, 2006, 11:39 PM

I don't think an export to xml is a good way to back up a database. A database like this can be backed up to sql files easier. But using a backup to a file is the overall easiest and most reliable method. I hope you have a backup of the DBF file, or a backup made within sql server to a file?

The problem is how to restore it while maintaining ref integrity - it's not going to be easy. From a file, however, this is automatic and will be right.

A leading 0 shouldn't matter. What's the difference between 01 and 1? Your problems are going to be elsewhere. And perhaps many of them :-(

Maxxim · November 13th, 2006, 02:09 AM

Thanks Englere....

The server where i hosted my site, does not permit direct connections to sql mannager! The admin panel to mannage mssql is "SQL Server Web Admin"

My dream is exporting all my db to one single file but I don't know how to do it...

In "SQL Server Web Admin" I can only export tables using "xls, xml, csv"

I use xml, because it will generate a schema with all the information table and i made a script that reads this xml and give all the insertion code...

"inser into table (zzz,zzz,zzz) Values (1,NULL,'Value')
...
...
"

The only thing i had to do more was a script that i run in sql and gives me what tables do i have to cancel the constraints and the identify column before inserting values... ;)

This process works... only passwords don't work :( I don't know why

But you say to export all db information to a single file...
How do you do that ?

I only know "backup database and restore database" to do that

englere · November 13th, 2006, 08:55 PM

A standard SQL backup to a file will do the job for you. You need admin rights on the server to pull this off, but your hosting provider will do it for you uness you have a free site. They can do the backup for you and send you the file, or provide a download link where you can get the file. All professional hosting companies will do this, even if you're switching to a new company. But the free services may not do it.

Instead of doing this, you could also use a "data pump" to read the data from your hosting server and write it to your local DB. This handles blob data and constraints for you. I made this kind of program for the 2nd edition but it should work for ThePhile with only a small number of changes (like table names and connection strings). This is provided without any support, so use it if you want to:
http://www.ericengler.com/downloads/...bleData.cs.txt

Eric

Maxxim · November 16th, 2006, 09:43 PM

Eric, It was good that all the paid company were thus!!

I'm removing my site from freewebdot, and rent a hosting plan with godaddy.com

But they function in the same way !

They have the same "SQL SERVER WEB ADMIN" and the only way they provide to import the comlumn contents is:

- query
- csv file

:(

I made a single query and run it in my local mssql manager and it works fine (whitout talking about the passwords error in binary field)
But I tried to run this same query in they query window and receive a thousand errors...

I asked if they can receive my "backup" file and make a restore from there but... they say that i can make all this with "csv file" and if have some problems i can send csv file to them...

The csv file don't allow to insert identity fields... so, if i have topicid linked to replyid and categoryid, and accounts_users linked to memberid, all this IDs will be lost with a csv import. Am I Wrong?

Are this the way that a big company like www.godaddy.com works?
It's normal?

Thanks for your patience!
Max

englere · November 18th, 2006, 08:06 AM

I guess you didn't want to try the CopyTableData program? That may need a few small tweaks for .net 1.1, but it will do the job. Or, you can run it using 2.0 against SQL 2000. You have to understand that you can connect to your remote DB using ADO.NET on your own PC, so that means you can copy the data to your own PC using a .NET program running on your PC.

GoDaddy is known for being a domain registrar. They switched to Windows about a year ago. They're not run by programmer geeks, unlike DiscountASP.NET and WebHost4Life.

Maxxim · November 19th, 2006, 05:26 PM

Thanks one more time Englere!

I'm considering to use your script...

If you have some time, please try to reply this doubt:

I don't know that I could have a localhost application and connect with my online sql database.

I'll not using this for backup my old database because that one i have it in my local bd!
But i'm going to try use this script to export all my db content...

I tried to acess my online db from my localhost application:

I made an asp.net application, using this connectionstring:

server=whsql-v12.prod.mesa1.secureserver.net;database=myDBname; uid=myuserlogin;pwd=mypass

and then I try to populate a datagrid with a test table that i created in my online db

But I receive an error:

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

It's logic because my online db don't allow external connection so... How do you connect to the external db with this script? I don't understand... If you can connect with this console application you sould have sucess connecting through your mssql mannager and then this script won't be necessary...

Please, if you have some time, elucidates me please!

Thank you very much!
Max

englere · November 21st, 2006, 10:54 PM

You could really benefit from some books on SQL Server.

In almost all cases, you can connect to a hosted DB over the internet. And, of course you can connect to a local DB.

And you can connect to them both with Query Analyzer, but Enterprise manager will be limited in what it can do on the remote db because you don't have admin priv on the DB server computer.

How do you think you can script the data, including the binary fields like the encrypted password? There ARE ways of doing that, but not very easy ways. That's why the console program is easier. SqlServerCentral.com has scripts for this, but you have to understand it pretty good to use them.

And why can't you create an extra local DB to hold a copy of your production data? You are allowed to have more than 1 - every SQL Server has more than 1 DB out of the box, and you can add any number of new ones.

But this is hard for me to explain to you in email messages. You should do some web research and get some books. And maybe you know someone locally who can sit down with you and show you some things?

Eric

Maxxim · November 22nd, 2006, 07:13 PM

Englere, thanks a lot. I really admire you because without you this forum....

I understand that email messages don't allow to talk about "all" stuff behind sql!

I'm Portuguese, and all my friends are turned to "php" and "mysql"

But in this case I think I have two possible ways:

- Make an application like yours (or use yours) and run it from my localhost and point my sql area on godaddy. But to do that I have to Know the IP where my online db is... (I don't know that)
- Make the application, put it online, disable my firewall to permit external connections to my sql server, and point my local db and make the transfer...

I almost complete the second choice, but since last 2 days I had crossing mails with goDaddy to find solution to this problem!

You are right when You says that are many ways to connect with DB...
MSSQL Mannager is out question because don't allow connections, but when you talk about query admin it's complicated...
Because if I had acess to query analyzer where my db this problem was resolved already BUT the solution that they provide to substitute "query analyzer" Is a query window in the "sql server admin" (I don't know if you know this program that runs in any browser)

To finish this issue, I had made a single sql file with instructions to create my db structure and insert all fields (binary... varchar... int... text etc...) and mantain identity fileds etc...

But this file run without problems in my local query analyzer but don't run in same way in their query window!

After two days they received my file and run it in direct query analyzer and the problem is solved!
So the problem wasn't mine. It's the bad query window that they provide!!

Thanks a lot one more time.

Max

englere · November 27th, 2006, 11:47 PM

Thanks for your post.

I don't think you need firewall changes on your system because you're making a call going out - nobody is calling in to your PC. The firewall is only used for incoming connections. Unless you have something set up differently.

Your hosted application has to connect to your hosted SQL Server, right? You must have a valid connection string in there somewhere! I *HOPE* they aren't auto-connecting you to a DB in app_data (that's fine for development, but a non-starter for professional deployment).

Eric