Scripting a SQL database

1. With Enterprise Manager what is required to have the script create the
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>

script for login transfer from one phy server to another

Hi,

Do you have any idea about the procedure for transferring the logins and password from source server to the destination server.

scenario is as follows

serv - A wanto transfer to ms sql 2000 server - B
running on 6.5 ------------> will be sql2k
(Old server) (new server)

will appriciate if any weblink containg the check list.

with best regards

ranjanI have a script that generates SQL for recreating logins, user, roles, and permissions on SQL 2000 databases. It may run on your 6.5 database.
It will not, however, restore passwords, and I'm not sure you can do this.

Have you thought about running the upgrade on your 6.5 databases (or a copy of them)? It's been a while, but I think this retains the old passwords.

blindman|||Thanx for reply,

Detail of my upgradation plan -

- I am planning to upgrade my db server (mssql-6.5) to server
(mssql2k) on the same machine.

- I have prepared the checklist for that , Only thing I want is a script
which will take care of transferring my ( login name & passoword)
from 6.5 server to 2000 server.

- will appriciate if you can help me in writting the script or foreward a
readymade script.

thanx regards

Ranjan|||Sorry, but I don't have a script for that. Are you sure that the upgrade process won't do this?
As a matter of fact, I think Microsoft changed its encryption algorythm between versions 6.5 and 2000, so even if you copied the encrypted value to the new server it might not recognize your password. If you are looking for a method of decrypting the password, I don't have one but I know it has been cracked (the algorythm was not very strong). You can probably find code for decrypting it on the web.

blindman|||Refer to this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q246/1/33.ASP&NoWebContent=1) to accomplish the task.

SCRIPT

Hi
I am very familiar with insert statements, but how does a
value get passed for a user name and password from either
a web or windows application i.e
A simple insert statement would be
Insert into Table1
(username,password)values('bob','password')
but if you do not know the values of the username and
password are going to be then what would the query look
like.
So if I plan to make my password banana when I log onto
the application how would the script look like then,
would you leave the '' where banana would be as blank.
Insert into Table1
(username,password)values('bob','')
Thanks
Bob
BOB wrote:
> Hi
> I am very familiar with insert statements, but how does a
> value get passed for a user name and password from either
> a web or windows application i.e
> A simple insert statement would be
> Insert into Table1
> (username,password)values('bob','password')
> but if you do not know the values of the username and
> password are going to be then what would the query look
> like.
> So if I plan to make my password banana when I log onto
> the application how would the script look like then,
> would you leave the '' where banana would be as blank.
> Insert into Table1
> (username,password)values('bob','')
>
> Thanks
> Bob
First, we might enquire why you are storing user names and passwords in
a table. Can you rely on SQL Server's security mechanisms instead?
In general, though, is you have an attribute (column) in a table that
may contain unknown data, you allow NULL values. And you insert a NULL
or leave the column out of the insert operation altogether.
For web-based security, it's best to use Windows Authentication. Then no
passwords need to be sent at all. Some web servers may use hard-coded
connection strings to the server, but it's better to use Windows
Authentication.
Can you provide more detail about what you're trying to accomplish?
David Gugick
Imceda Software
www.imceda.com

scramble password

Hello group,
I have a fairly simple question (hopefully simple anyway). I have a table
that has basic application user data: name, address, city, email, password.
I now have a requirement to in bulk take the password and encrypt them. The
y
have already given me the function name to use: ToBase64Sting().
Here is the question; how can I run the password field into this function in
a stored procedure? I am not familier with this function but have done some
reading. I suspect I would just place the scrambled password into a second
field and delete the original, then rename the field. Any suggestions?update YourTable
set password = dbo.ToBase64Sting(password)
try doing this first and look at the the 2 fields
select password ,dbo.ToBase64Sting(password ) from YourTable
http://pixsells.blogspot.com
"Rich" wrote:

> Hello group,
> I have a fairly simple question (hopefully simple anyway). I have a table
> that has basic application user data: name, address, city, email, password
.
> I now have a requirement to in bulk take the password and encrypt them. T
hey
> have already given me the function name to use: ToBase64Sting().
> Here is the question; how can I run the password field into this function
in
> a stored procedure? I am not familier with this function but have done so
me
> reading. I suspect I would just place the scrambled password into a secon
d
> field and delete the original, then rename the field. Any suggestions?|||Rich wrote:
> Hello group,
> I have a fairly simple question (hopefully simple anyway). I have a
> table that has basic application user data: name, address, city,
> email, password. I now have a requirement to in bulk take the
> password and encrypt them. They have already given me the function
> name to use: ToBase64Sting().
> Here is the question; how can I run the password field into this
> function in a stored procedure? I am not familier with this function
> but have done some reading. I suspect I would just place the
> scrambled password into a second field and delete the original, then
> rename the field. Any suggestions?
Is the function they gave you a SQL Server callable function? If so, you
can just update the table directly, assuming the return value from the
function (which is likely just ASCII) is compatible with the data type
you are using for the column in the table. Something like:
Update
dbo.MyTable
Set
Password = dbo.ToBase64String(Password)
BTW, why are you storing passwords in the database? Base64 is not an
encryption scheme. It is an encoding scheme, used mainly by email system
to send attachments. Base64 is not secure and anyone with internet
access will be able to turn that Base64 value into the original
password. So, you may want to reconsider storing passwords in the
database.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hello David,
see http://support.microsoft.com/defaul...kb;en-us;317535
I thought that this function would be available from the SQL Server however
this function does not appear to be part of TSQL. I tried the code fragment
you posted and I am thinking this will not work. This functionis something
the my web developers I work with are using so you comment about being secur
e
over the web scares me...
Rich
"David Gugick" wrote:

> Rich wrote:
> Is the function they gave you a SQL Server callable function? If so, you
> can just update the table directly, assuming the return value from the
> function (which is likely just ASCII) is compatible with the data type
> you are using for the column in the table. Something like:
> Update
> dbo.MyTable
> Set
> Password = dbo.ToBase64String(Password)
> BTW, why are you storing passwords in the database? Base64 is not an
> encryption scheme. It is an encoding scheme, used mainly by email system
> to send attachments. Base64 is not secure and anyone with internet
> access will be able to turn that Base64 value into the original
> password. So, you may want to reconsider storing passwords in the
> database.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||I see what you are doing we used to do the same
You use .NET encryption the function you mentioned is a .NET function
Have one of your web guys write a script to loop through the table and
update the password with the encrypted string
There is no way you can do this in SQL server 2000 (as far as I know that is
)
http://sqlservercode.blogspot.com/
"Rich" wrote:
> Hello David,
> see http://support.microsoft.com/defaul...kb;en-us;317535
> I thought that this function would be available from the SQL Server howeve
r
> this function does not appear to be part of TSQL. I tried the code fragme
nt
> you posted and I am thinking this will not work. This functionis somethin
g
> the my web developers I work with are using so you comment about being sec
ure
> over the web scares me...
> Rich
> "David Gugick" wrote:
>|||Hello SQL,
yes, yes, yes, you understand! I just need a loop to update that field with
that function they suggested! The problem is I will end up make the "loop".
I am looking at making this looper in MSAccess, connect to the table, run th
e
loop and be done with this. I just need to understand if this is a standard
VB function that I can drop the field into the function and let the thing
return the encrypted password.
Rich
"SQL" wrote:
> I see what you are doing we used to do the same
> You use .NET encryption the function you mentioned is a .NET function
> Have one of your web guys write a script to loop through the table and
> update the password with the encrypted string
> There is no way you can do this in SQL server 2000 (as far as I know that
is)
> http://sqlservercode.blogspot.com/
>
> "Rich" wrote:
>|||I don't know if MS Access will work
We used C# ASP.NET but I was not involved with this process
I don't know if MS Access can access those functions since they are .NET
specific
try one of the .NET newsgroups for this question
http://sqlservercode.blogspot.com/
"Rich" wrote:
> Hello SQL,
> yes, yes, yes, you understand! I just need a loop to update that field wi
th
> that function they suggested! The problem is I will end up make the "loop
".
> I am looking at making this looper in MSAccess, connect to the table, run
the
> loop and be done with this. I just need to understand if this is a standa
rd
> VB function that I can drop the field into the function and let the thing
> return the encrypted password.
> Rich
> "SQL" wrote:
>|||Store a hash of the password not the password itself. Hash the input and
compare both hashes to determine if they are the same. There are some
undocumented hash functions in SQL (don't remember the names) but you really
should hash it then send it to the server for comparison rather than send
the plain text password across the wire.
Derek Davis
ddavis76@.gmail.com
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:F32DD3BB-A829-456F-AF2A-18AA6D1FEEDB@.microsoft.com...
>I don't know if MS Access will work
> We used C# ASP.NET but I was not involved with this process
> I don't know if MS Access can access those functions since they are .NET
> specific
> try one of the .NET newsgroups for this question
> http://sqlservercode.blogspot.com/
>
> "Rich" wrote:
>