Basic Question about password and logon command

I have running SQL queries all day and have been getting tired of selecting the DATA SOURCE NAME and then having to type in the appropriate password. What can I add to my SQL command to do this for me?
Thanks for the help!
NormDo you mean like a connect line in your script?|||Originally posted by exdter
Do you mean like a connect line in your script?

Yes, exactly|||Is this what you mean?
The first line of your script should be
conn[ect] user/password/database
The problem with this is that your password won't be encrypted, so someone can see it.
I hope this helps.|||Originally posted by exdter
Is this what you mean?
The first line of your script should be
conn[ect] user/password/database
The problem with this is that your password won't be encrypted, so someone can see it.
I hope this helps.

Forgive my ignorance but I don't think I asked for the right thing.

Here's what I'm looking at....

I'm in Access and I run an SQL query such as:

select COUNT(*) from employee_data where title = 'Programmer'

and the next thing you know is a window pops up where I need to click on the "Machine Data Source" tab and then the appropriate "Data Source Name" which then has me type in my password.

Thanks for the help.

Norm|||Oh, sorry. I can't help with that one.
I don't know Access.

Bad Login/Password...how to correct it?

How do I change the login/password Reporting Services uses to connect to
SQL Server?
How do I reconfigure the options I chose when I installed SQL Server
Reporting Services in the first place?
Help!?!?rsconfig.exe -c
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JC Ford" <johncford@.hotmail.com> wrote in message
news:eSa0kGvfEHA.592@.TK2MSFTNGP11.phx.gbl...
> How do I change the login/password Reporting Services uses to connect to
> SQL Server?
> How do I reconfigure the options I chose when I installed SQL Server
> Reporting Services in the first place?
> Help!?!?|||Thanks!
Tudor Trufinescu (MSFT) wrote:
> rsconfig.exe -c
>

backup\restore password?

How and where in the GUI (Enterprise Manager) can I specify a password for
restoring databases?
Like this:
1. Create db backup job
2. set pwd
3. restore db, by specifying the pwd set in step 2
Please, if possible, show me a screen shot.
Thanks!
MagnusYou can't do it through Enterprise Manager. You need to use
T-SQL backup, restore commands. Or use SQL-DMO which also
exposes the password property through the Backup2 object
(MediaPassword property).
-Sue
On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
<magnus_osterberg@.hotmail.com> wrote:

>How and where in the GUI (Enterprise Manager) can I specify a password for
>restoring databases?
>Like this:
>1. Create db backup job
>2. set pwd
>3. restore db, by specifying the pwd set in step 2
>Please, if possible, show me a screen shot.
>Thanks!
>Magnus
>|||In QA, you can make a T-SQL using password.
BACKUP DATABASE Northwind TO MyNwind_1
WITH PASSWORD='password'
RESTORE DATABASE MyNwind FROM MyNwind_1
WITH PASSWORD='password'
"Magnus ?sterberg"?? ??? ??:

> How and where in the GUI (Enterprise Manager) can I specify a password for
> restoring databases?
> Like this:
> 1. Create db backup job
> 2. set pwd
> 3. restore db, by specifying the pwd set in step 2
> Please, if possible, show me a screen shot.
> Thanks!
> Magnus
>
>|||OK, now I found out how to add this to my SQL Server Agent backup job. But..
.the pwd is stored as a part of the SQL statement in plain text. Any sa-user
can easily check up the pwd by opening the job.
Mayby one can also schedule the job by T-SQL? As in:
BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY' WITH I
NIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS = 10, NOFORMAT, PASS
WORD='sjkf7wed8', SCHEDULE=daily, 21:00
...sounds a bit far-fetched. But my idea is that I could execute the T-SQL
statement once, which should store the job in the DBMSS, without allowing an
yone to see the pwd. Possible?
Mayby I have to take care of the scheduling myself?
Magnus
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:fjre5254k8q0e06t68tnmrhthsigu70
57u@.4ax.com...
> You can't do it through Enterprise Manager. You need to use
> T-SQL backup, restore commands. Or use SQL-DMO which also
> exposes the password property through the Backup2 object
> (MediaPassword property).
>
> -Sue
>
> On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
> <magnus_osterberg@.hotmail.com> wrote:
>
>|||anyone got an answer to this one'
"Magnus sterberg" <magnus_osterberg@.hotmail.com> wrote in message news:u2XH
eGobGHA.4040@.TK2MSFTNGP02.phx.gbl...
OK, now I found out how to add this to my SQL Server Agent backup job. But..
.the pwd is stored as a part of the SQL statement in plain text. Any sa-user
can easily check up the pwd by opening the job.
Mayby one can also schedule the job by T-SQL? As in:
BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY' WITH I
NIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS = 10, NOFORMAT, PASS
WORD='sjkf7wed8', SCHEDULE=daily, 21:00
..sounds a bit far-fetched. But my idea is that I could execute the T-SQL s
tatement once, which should store the job in the DBMSS, without allowing any
one to see the pwd. Possible?
Mayby I have to take care of the scheduling myself?
Magnus
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:fjre5254k8q0e06t68tnmrhthsigu70
57u@.4ax.com...
> You can't do it through Enterprise Manager. You need to use
> T-SQL backup, restore commands. Or use SQL-DMO which also
> exposes the password property through the Backup2 object
> (MediaPassword property).
>
> -Sue
>
> On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
> <magnus_osterberg@.hotmail.com> wrote:
>
>|||Can't do that. Even if you could execute a statement once and have it
lie dormant inside the DBMS somehow, a sysadmin can still look in the
proc cache and see SQL statements & also at the current processes to see
what statements are running (including any sensitive information
included in the statement batch).
The closest you could come to that with pure SQL (and not some 3rd party
tool) would be to write a stored proc that contains an infinite WHILE
loop (eg. "WHILE 1=1") and in the loop you'd have the backup statement
and a "WAITFOR TIME '2:00:00'" statement (see BOL), which would put the
thread to sleep until 2am for example at which time the thread would
wake up, do the backup, loop and go to sleep again. Then you'd get SQL
Server to run the proc on start up (see sp_procoption in BOL) and you
could create the proc with the WITH ENCRYPTION option to make it a
little harder to see the password embedded in the proc. However, a
sysadmin would still be able to see that T-SQL code by querying the
appropriate system metadata (proc cache and/or current processes).
What's the point of password protecting a backup so that the sysadmins
on the box cannot restore that database when they can see the real
online live version of the DB anyway? Seems like not a very credible
situation. Why would you make a login a sysadmin if they're not allowed
to see the data in one of the databases? Isn't that the point of NDAs
(non-disclosure agreements) for DBAs?
*mike hodgson*
http://sqlnerd.blogspot.com
Magnus sterberg wrote:

> anyone got an answer to this one'
>
> "Magnus sterberg" <magnus_osterberg@.hotmail.com
> <mailto:magnus_osterberg@.hotmail.com>> wrote in message
> news:u2XHeGobGHA.4040@.TK2MSFTNGP02.phx.gbl...
> OK, now I found out how to add this to my SQL Server Agent backup
> job. But...the pwd is stored as a part of the SQL statement in
> plain text. Any sa-user can easily check up the pwd by opening the
> job.
> Mayby one can also schedule the job by T-SQL? As in:
> BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY'
> WITH INIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS =
> 10, NOFORMAT, PASSWORD='sjkf7wed8', *SCHEDULE=daily, 21:00*
> **
> ...sounds a bit far-fetched. But my idea is that I could execute
> the T-SQL statement once, which should store the job in the DBMSS,
> without allowing anyone to see the pwd. Possible?
> Mayby I have to take care of the scheduling myself?
>
> Magnus
> "Sue Hoegemeier" <Sue_H@.nomail.please
> <mailto:Sue_H@.nomail.please>> wrote in message
> news:fjre5254k8q0e06t68tnmrhthsigu7057u@.
4ax.com...
> <mailto:magnus_osterberg@.hotmail.com>> wrote:
> password for
>

Backup to a share

Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
a session on both machines is "Administrator", "password". SQL server account
is the same (administrator).
I on the sql box, I create a share to the 2nd box. I can create, deleteor do
anything on this share. However, when I create a maintenance plan to backup
some DBs to this share, the job fails.
I tried UNC,it did not work neither.
How can or is it possible to backup to a share?
ThanksOf course this is possible. Start by giving permissions on that share to the
Windows account used by the SQL Server service, that is, that account must be
able to write files to that share. Connect as that account and make sure you
have access to the share.
If the job still does not work go to the View History section of the job and
look for error messages. Also take a look at the SQL Server error log.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"SalamElias" wrote:
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> a session on both machines is "Administrator", "password". SQL server account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> anything on this share. However, when I create a maintenance plan to backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||SQL Server itself (and SQL Server Agent) are not running as Administrator,
are they? When you map a drive letter, it is mapped for YOUR username only.
This drive letter is not visible to other users (e.g. the user that SQL
services run as).
When you tried a UNC path, what does "did not work" mean? Do you get an
error message? If so, what is it?
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
For SQL 2000, but it works just the same for SQL 2005 as far as required
permissions.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks|||As I said, both credetnials are the same and I can create, delete modify any
file in the share which are used to run both services SQL server and agnet
When editing the MP, in the backup task page, I click on the button with 3
dots opposite to folder, I see only the local disks C:\ and E:\ not the
mapped share Z:\backup.
Regards
"Ben Nevarez" wrote:
> Of course this is possible. Start by giving permissions on that share to the
> Windows account used by the SQL Server service, that is, that account must be
> able to write files to that share. Connect as that account and make sure you
> have access to the share.
> If the job still does not work go to the View History section of the job and
> look for error messages. Also take a look at the SQL Server error log.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "SalamElias" wrote:
> > Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> > a session on both machines is "Administrator", "password". SQL server account
> > is the same (administrator).
> > I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> > anything on this share. However, when I create a maintenance plan to backup
> > some DBs to this share, the job fails.
> > I tried UNC,it did not work neither.
> > How can or is it possible to backup to a share?
> >
> > Thanks

Backup to a share

Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
a session on both machines is "Administrator", "password". SQL server account
is the same (administrator).
I on the sql box, I create a share to the 2nd box. I can create, deleteor do
anything on this share. However, when I create a maintenance plan to backup
some DBs to this share, the job fails.
I tried UNC,it did not work neither.
How can or is it possible to backup to a share?
Thanks
Of course this is possible. Start by giving permissions on that share to the
Windows account used by the SQL Server service, that is, that account must be
able to write files to that share. Connect as that account and make sure you
have access to the share.
If the job still does not work go to the View History section of the job and
look for error messages. Also take a look at the SQL Server error log.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"SalamElias" wrote:

> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to open
> a session on both machines is "Administrator", "password". SQL server account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor do
> anything on this share. However, when I create a maintenance plan to backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||SQL Server itself (and SQL Server Agent) are not running as Administrator,
are they? When you map a drive letter, it is mapped for YOUR username only.
This drive letter is not visible to other users (e.g. the user that SQL
services run as).
When you tried a UNC path, what does "did not work" mean? Do you get an
error message? If so, what is it?
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
For SQL 2000, but it works just the same for SQL 2005 as far as required
permissions.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"SalamElias" <eliassal@.online.nospam> wrote in message
news:56435BAD-689C-468E-AE71-894760DB63BD@.microsoft.com...
> Hi, I have 2 win2k3, they are in a workgroup and the credential I use to
> open
> a session on both machines is "Administrator", "password". SQL server
> account
> is the same (administrator).
> I on the sql box, I create a share to the 2nd box. I can create, deleteor
> do
> anything on this share. However, when I create a maintenance plan to
> backup
> some DBs to this share, the job fails.
> I tried UNC,it did not work neither.
> How can or is it possible to backup to a share?
> Thanks
|||As I said, both credetnials are the same and I can create, delete modify any
file in the share which are used to run both services SQL server and agnet
When editing the MP, in the backup task page, I click on the button with 3
dots opposite to folder, I see only the local disks C:\ and E:\ not the
mapped share Z:\backup.
Regards
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Of course this is possible. Start by giving permissions on that share to the
> Windows account used by the SQL Server service, that is, that account must be
> able to write files to that share. Connect as that account and make sure you
> have access to the share.
> If the job still does not work go to the View History section of the job and
> look for error messages. Also take a look at the SQL Server error log.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "SalamElias" wrote: