Encrypting the configuration file values stored in SQL server

Hi All,

I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.

If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.

Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?

Thanks In Advance,

Madhu

I think the short answer to this is no, and no code hooks either.

I think though that there is also an argument, that says it would not be more secure than what you have now. If you encrypt the data, you need to then secure the key. So what will you do to secure the key? Why not use strong security to secure the password data instead of worrying about how to secure the key? I accept that the encryption adds an extra step, but I'm not convinced it will actually be any safer.

|||I'm not sure if it's a good idea, but couldn't he create a script task to decrypt the password and reset the connection manager's connectionstring property before the connection manager is used in the package?|||

Yes and no. Some connections are used before your script task could run, such as connections used for logging.

How would you secure the key used to decrypt the password? You need to secure the encryption/decryption key, so why not just secure the password to start with?

|||DarrenSQLIS is right the recommended way to do this is to store the password in the connection. SSIS will automatically encrypt these so that they are not stored in cleartext.|||Thanks for the thoughts Darren. As suggested by you, way to go is to store the password in SQL server and make sure that the access to the configuration table is only for administrators.|||

Denise, I think you are talking about the package level encryption, protection levels and such like. Nice though it is, it is not very useful, as I think you should "externalise" any kind of security information.

Using package encryption becomes unfeasible when you have to migrate packages between environments. Configurations solve that migration issue, but don't give you the encryption that is often seen as a requirement for some organisations. I'd argue that is should not be a big deal, secure the password so you don't have to worry about the key, but often it is an internal "standard" that must be complied with.

Still we have the choice of package encryption, which is better than not!

Encrypt and Decrypt Data - Subscription

Hi All,
I am subsrcibing a report through the Report Manager, before sending the
mail, i need to encrypt the data in the report and send it to the user.
I can do the encryption but passing the data to a function in a custom
assembly and then return the encrypted data to the report and then send a
mail.
But at the receiver end, the report data should be decrypted and display in
the mail when the user open the subscribed mail.
Any inputs..
Thanks
Balaji
--
Message posted via http://www.sqlmonster.comIn this scenario your clients would have to have your decrypt utility. And
that would only work if your export format is one of the text based ones
(csv, xml, etc) and not one of the image based ones (tiff, pdf, etc).
What I did was, based on my application logic, create subscriptions, have
reports saved to a file share, have a windows service process take the
report and save it in an image field in the database. To get the report the
user logs into my web app (which is https and secure) and retrieves the
report.
--
Adrian M.
MCP
"BALAJI via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:bd5744926a0e4a00b632863dfb28dc20@.SQLMonster.com...
> Hi All,
> I am subsrcibing a report through the Report Manager, before sending the
> mail, i need to encrypt the data in the report and send it to the user.
> I can do the encryption but passing the data to a function in a custom
> assembly and then return the encrypted data to the report and then send a
> mail.
> But at the receiver end, the report data should be decrypted and display
> in
> the mail when the user open the subscribed mail.
> Any inputs..
> Thanks
> Balaji
> --
> Message posted via http://www.sqlmonster.com

Enabling SQL Server Agent by using scripts

Hi NG,
how do I have to enable the SQL Server Agent by using command line scripts
(without the Enterprise Manager). According to SQLServer books online the
command line command sqlagent should only be used for diagnostic purposes.
thanks for help, reneRene,
To start SQL Server Agent from the commandline you can do this:
net start sqlserveragent
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Re Fo wrote:
> Hi NG,
> how do I have to enable the SQL Server Agent by using command line scripts
> (without the Enterprise Manager). According to SQLServer books online the
> command line command sqlagent should only be used for diagnostic purposes.
> thanks for help, rene
>|||Thanks,
can I set it somewhere in the registry that the service will alwyas be
started, as I can set it in the Services properties?
Thanks for help, rene
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:ec0Gbwf4EHA.1976@.TK2MSFTNGP09.phx.gbl...
> Rene,
> To start SQL Server Agent from the commandline you can do this:
> net start sqlserveragent
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Re Fo wrote:
> > Hi NG,
> >
> > how do I have to enable the SQL Server Agent by using command line
scripts
> > (without the Enterprise Manager). According to SQLServer books online
the
> > command line command sqlagent should only be used for diagnostic
purposes.
> >
> > thanks for help, rene
> >
> >|||There are two places you can do this.
1)
Enterprise Manager-->General Tab, Tick AutoStart SQL Server Agent
2)
In Services Control Panel Applet, you can set the SQLServerAgent service
to AutoStart.
or if these don't work, you could even do this:
3)
CREATE PROC sp_startagent
AS
exec master..xp_cmdshell 'net start sqlserveragent', no_output
GO
exec sp_procoption N'sp_startagent', N'startup', N'true'
GO
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Re Fo wrote:
> Thanks,
> can I set it somewhere in the registry that the service will alwyas be
> started, as I can set it in the Services properties?
> Thanks for help, rene
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:ec0Gbwf4EHA.1976@.TK2MSFTNGP09.phx.gbl...
>>Rene,
>>To start SQL Server Agent from the commandline you can do this:
>>net start sqlserveragent
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>Re Fo wrote:
>>Hi NG,
>>how do I have to enable the SQL Server Agent by using command line
> scripts
>>(without the Enterprise Manager). According to SQLServer books online
> the
>>command line command sqlagent should only be used for diagnostic
> purposes.
>>thanks for help, rene
>>
>
>

Enabling SQL Server Agent by using scripts

Hi NG,
how do I have to enable the SQL Server Agent by using command line scripts
(without the Enterprise Manager). According to SQLServer books online the
command line command sqlagent should only be used for diagnostic purposes.
thanks for help, rene
Rene,
To start SQL Server Agent from the commandline you can do this:
net start sqlserveragent
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Re Fo wrote:
> Hi NG,
> how do I have to enable the SQL Server Agent by using command line scripts
> (without the Enterprise Manager). According to SQLServer books online the
> command line command sqlagent should only be used for diagnostic purposes.
> thanks for help, rene
>
|||Thanks,
can I set it somewhere in the registry that the service will alwyas be
started, as I can set it in the Services properties?
Thanks for help, rene
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:ec0Gbwf4EHA.1976@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Rene,
> To start SQL Server Agent from the commandline you can do this:
> net start sqlserveragent
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Re Fo wrote:
scripts[vbcol=seagreen]
the[vbcol=seagreen]
purposes.[vbcol=seagreen]
|||There are two places you can do this.
1)
Enterprise Manager-->General Tab, Tick AutoStart SQL Server Agent
2)
In Services Control Panel Applet, you can set the SQLServerAgent service
to AutoStart.
or if these don't work, you could even do this:
3)
CREATE PROC sp_startagent
AS
exec master..xp_cmdshell 'net start sqlserveragent', no_output
GO
exec sp_procoption N'sp_startagent', N'startup', N'true'
GO
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Re Fo wrote:
> Thanks,
> can I set it somewhere in the registry that the service will alwyas be
> started, as I can set it in the Services properties?
> Thanks for help, rene
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:ec0Gbwf4EHA.1976@.TK2MSFTNGP09.phx.gbl...
>
> scripts
>
> the
>
> purposes.
>
>

Enabling Replication Question

Can I enable replication from a different server that has Enterprise
Manager install than the one that hosts the SQL database without any
problems or side effects?
I cannot enable replication from my server (Server A) hosting SQL
Server because the registration name is (local) instead of the server
name. Yes I know I can delete the registration and re-register it
with the server name BUT my ERP software vendor warns me by doing I
will break some of their core processing functionality because it
relies on the SQL Server registration name.
So to avoid this problem my though would be to enable replication from
(Server B) that has SQL Server Enterprise Manager installed and points
to Server A. My question is this - Can I successfully enable
replication through this process or will I encounter other problems or
create other issues?
I'm running SQL Server 2000.
Thanks for any information, comments or generally better ideas!Hi,
You can register server A in the Enterprise Manager on ANY machine
that has Enterprise Manager (and network access) -- including server B
-- and set up replication, or any other SQL administration tasks, on
server A.
Ken
On Feb 21, 5:31 pm, "dfrazell" <dfraz...@.estoneworks.com> wrote:
> Can I enable replication from a different server that has Enterprise
> Manager install than the one that hosts the SQL database without any
> problems or side effects?
> I cannot enable replication from my server (Server A) hosting SQL
> Server because the registration name is (local) instead of the server
> name. Yes I know I can delete the registration and re-register it
> with the server name BUT my ERP software vendor warns me by doing I
> will break some of their core processing functionality because it
> relies on the SQL Server registration name.
> So to avoid this problem my though would be to enable replication from
> (Server B) that has SQL Server Enterprise Manager installed and points
> to Server A. My question is this - Can I successfully enable
> replication through this process or will I encounter other problems or
> create other issues?
> I'm running SQL Server 2000.
> Thanks for any information, comments or generally better ideas!|||On Feb 21, 9:36 pm, "KenJ" <kenjohn...@.hotmail.com> wrote:
> Hi,
> You can register server A in the Enterprise Manager on ANY machine
> that has Enterprise Manager (and network access) -- including server B
> -- and set up replication, or any other SQL administration tasks, on
> server A.
> Ken
> On Feb 21, 5:31 pm, "dfrazell" <dfraz...@.estoneworks.com> wrote:
> > Can I enable replication from a different server that has Enterprise
> > Manager install than the one that hosts the SQL database without any
> > problems or side effects?
> > I cannot enable replication from my server (Server A) hosting SQL
> > Server because the registration name is (local) instead of the server
> > name. Yes I know I can delete the registration and re-register it
> > with the server name BUT my ERP software vendor warns me by doing I
> > will break some of their core processing functionality because it
> > relies on the SQL Server registration name.
> > So to avoid this problem my though would be to enable replication from
> > (Server B) that has SQL Server Enterprise Manager installed and points
> > to Server A. My question is this - Can I successfully enable
> > replication through this process or will I encounter other problems or
> > create other issues?
> > I'm running SQL Server 2000.
> > Thanks for any information, comments or generally better ideas!
So to clarify - I can enable replication on Server A from Server B
regardless of the SQL registration name of Server A without any side
effects?
Thanks - Dave|||As the server name of Server A will be the correct one when looking from
Server B's enterprise manager, I see no problems here provided you have a
sysadmin user. Some of the settings are local to the ServerA though (eg the
distribution working folder) so you might want to use a remote desktop as
well to set up the necessary share.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Enabling remote errors

Hi. I am trying to get remote errors to display in Report Manager. I've set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo table. Then I restarted both the Reporting Services Service and IIS. However, I am still seeing the following message when running a report:

An error has occurred during report processing.

What am I missing?

Thanks

Has anyone had a chance to look into this? Please let me know if you need more information. Thanks.

- Mark

|||

Check the reportserver logfiles (not the service logfiles!). They may contain more details about the error.

The logfiles are typically located in: \Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\Logfiles

-- Robert

|||

The problem has fixed itself. I am not sure how, but it did. I haven't made any changes. The only thing I did was reload my data processing extension. I doubt that's what fixed the problem because afterwards I was able to change the "EnableRemoteErrors" value back and forth and see the effect immediately, without restarting or reloading anything.

Strange...

|||

Hi,

I am pretty new to reporting services, can you please help me on how to set the 'EnableRemoteErrors' ?

Thanks in advance.

|||

Please read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230593&SiteID=1

-- Robert

Enabling remote errors

Hi. I am trying to get remote errors to display in Report Manager. I've set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo table. Then I restarted both the Reporting Services Service and IIS. However, I am still seeing the following message when running a report:

An error has occurred during report processing.

What am I missing?

Thanks

Has anyone had a chance to look into this? Please let me know if you need more information. Thanks.

- Mark

|||

Check the reportserver logfiles (not the service logfiles!). They may contain more details about the error.

The logfiles are typically located in: \Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\Logfiles

-- Robert

|||

The problem has fixed itself. I am not sure how, but it did. I haven't made any changes. The only thing I did was reload my data processing extension. I doubt that's what fixed the problem because afterwards I was able to change the "EnableRemoteErrors" value back and forth and see the effect immediately, without restarting or reloading anything.

Strange...

|||

Hi,

I am pretty new to reporting services, can you please help me on how to set the 'EnableRemoteErrors' ?

Thanks in advance.

|||

Please read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230593&SiteID=1

-- Robert

enabling AWE

After enabling awe and setting the max server memory, the
Task Manager, Process for sqlserver.exe shows 90MB - is
this normal? I have configured sql server to use 6GB!!!!
How can I be sure the memory is set up correctly?
CBUse perfmon and view the sql server memory counters instead of task manager.
Specifically the target and total memory should show approx 6GB. Did you
set the /PAE switch in the boot.ini as well? How about the /3GB?
87 Shrinking TempDB
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
--
Andrew J. Kelly SQL MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:64e201c4cb40$8dab3650$a601280a@.phx.gbl...
> After enabling awe and setting the max server memory, the
> Task Manager, Process for sqlserver.exe shows 90MB - is
> this normal? I have configured sql server to use 6GB!!!!
> How can I be sure the memory is set up correctly?
> CB

enabling AWE

After enabling awe and setting the max server memory, the
Task Manager, Process for sqlserver.exe shows 90MB - is
this normal? I have configured sql server to use 6GB!!!!
How can I be sure the memory is set up correctly?
CBUse perfmon and view the sql server memory counters instead of task manager.
Specifically the target and total memory should show approx 6GB. Did you
set the /PAE switch in the boot.ini as well? How about the /3GB?
87 Shrinking TempDB
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
Andrew J. Kelly SQL MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:64e201c4cb40$8dab3650$a601280a@.phx.gbl...
> After enabling awe and setting the max server memory, the
> Task Manager, Process for sqlserver.exe shows 90MB - is
> this normal? I have configured sql server to use 6GB!!!!
> How can I be sure the memory is set up correctly?
> CB

enabling AWE

After enabling awe and setting the max server memory, the
Task Manager, Process for sqlserver.exe shows 90MB - is
this normal? I have configured sql server to use 6GB!!!!
How can I be sure the memory is set up correctly?
CB
Use perfmon and view the sql server memory counters instead of task manager.
Specifically the target and total memory should show approx 6GB. Did you
set the /PAE switch in the boot.ini as well? How about the /3GB?
87 Shrinking TempDB
http://www.sql-server-performance.com/awe_memory.asp Using AWE Memory
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
http://www.support.microsoft.com/?id=274750 Memory config
http://www.support.microsoft.com/?id=283037 Large Memory Support Is
Available in Windows 2000 (AWE)
http://www.support.microsoft.com/?id=811891 Can not use more than 2GB of
memory
Andrew J. Kelly SQL MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:64e201c4cb40$8dab3650$a601280a@.phx.gbl...
> After enabling awe and setting the max server memory, the
> Task Manager, Process for sqlserver.exe shows 90MB - is
> this normal? I have configured sql server to use 6GB!!!!
> How can I be sure the memory is set up correctly?
> CB

EnableReportDesignClientDownload

Dear all
I would like to turn of the Report Builder Buttom in the Report Manager. It
worked with the following script out of msdn:
Class Sample
Public Shared Sub Main(ByVal prompt As String)
Dim rs As New ReportingService.ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim props(0) As [Property]
Dim setProp As New [Property]
setProp.Name = "EnableReportDesignClientDownload "
setProp.Value = prompt
props(0) = setProp
Try
rs.SetSystemProperties(props)
Catch ex As System.Web.Services.Protocols.SoapException
Console.Write(ex.Detail.InnerXml)
Catch e As Exception
Console.Write(e.Message)
End Try
End Sub 'Main
My problem is now, when I try to enable the Report Builder Buttom, this does
not work. Any idea why?
Thanks in advance for your answer,
Marc
--
Best regards,
MarcHello Marc,
Users must have permission to the Execute Report Definitions task (normally
obtained via the System Users role) for the Report Builder button to be
displayed.
See Home->Site Settings->Configure system-level role definitions->System
User->Execute Report Definitions
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter
it worked well thanks!
Best regards,
Marc|||Welcome!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Enable to start the Sql ENTERPRISE MANAGER

Can any one help me to solve this problem, I WAS ENABLE TO START THE Sql ENTERPRISE MANAGER

Anywhere i have try several way to make it right
1. unregister the sqldmo.dll file n register again
2. copy the ati.dll into antoher dir, but i fail to do it and this solution is provide by MSDN homepage.

i have reinstalll it 2 times, ....i hope i get the solution.

system using
WINDOWS XP servicepack 2
SQL SERVER 2000 Personal edition.
VS 2003 Enterprise

Thanks to microsoft and msdn forum MVP.|||

Hi,

i encountered the problem for myself several times, which was related due to an unregistered classid (whatever that triggered). Reinstalling or repairing the installation fixed the problem everytime. Are you also running the most current service pack ?

BTW: You have a very cool wallpaper on your desktop :-D

HTH, Jens Suessmeyer.

-
http://www.sqlserver2005.de
-

|||

I was failed to do it. I try all the solution provide by microsoft.

Everytime i install sql server 2000, at the end of the installation there will prompt out an error

Fail to register Active X component

Error : 0X80020009

:'(

|||

for your information

i have try to unregister n register the following dll

sqlns.dll

sqlmmc.dll

sqllex.dll

sqldmo.dll

dtsur.dll

odbcbcp.dll

but still not working

|||Have you tried adding the SQL Server Enterprise Manager by creating your own MMC Snap-In?|||

Oh yeah ....i fail to register the sqlmmc.dll. but i sucessfull unregister it.

so it is cause me this problem?

the error message is 0x80004005

EMs Database Space Available Wrong

Anyone ever see a case where the Space Available number
reported in Enterprise Manager's General Database
Properties tab is wrong?
My database has the following characteristics:
-1- Has a Daily Backup Maintenance plan (no other
optimizations such as data reorg and such)
-2- Simple Recovery Model
-3- Auto Update Statistics = on
-4- Auto Create Statistics = on
-5- All other settings on Options tab = off
I went and increased the data file to 500 MB larger than
the current size and still do not see Available space
increase. It's sitting at 0.
Any explanations/assistance would be greatly
appreciated. Thanks, BobSee DBCC UPDATEUSAGE in SQL Server 2000 Books Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bob Albert" <bob.albert@.quest.com> wrote in message
news:4d7b01c524de$84e83fc0$a501280a@.phx.gbl...
> Anyone ever see a case where the Space Available number
> reported in Enterprise Manager's General Database
> Properties tab is wrong?
> My database has the following characteristics:
> -1- Has a Daily Backup Maintenance plan (no other
> optimizations such as data reorg and such)
> -2- Simple Recovery Model
> -3- Auto Update Statistics = on
> -4- Auto Create Statistics = on
> -5- All other settings on Options tab = off
> I went and increased the data file to 500 MB larger than
> the current size and still do not see Available space
> increase. It's sitting at 0.
> Any explanations/assistance would be greatly
> appreciated. Thanks, Bob|||That's worked! Thank you so much!
Bob
>--Original Message--
>See DBCC UPDATEUSAGE in SQL Server 2000 Books Online.
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Bob Albert" <bob.albert@.quest.com> wrote in message
>news:4d7b01c524de$84e83fc0$a501280a@.phx.gbl...
than[vbcol=seagreen]
>
>.
>|||> Anyone ever see a case where the Space Available number
> reported in Enterprise Manager's General Database
> Properties tab is wrong?
Yes, Enterprise Manager is often wrong because it relies on cached data for
a lot of its values. If you want a reliable space used report, use EXEC
sp_spaceused in Query Analyzer, not Enterprise Manager. Also check into the
@.updateusage parameter. (sp_spaceused is well documented in Books Online).
Aaron|||Narayana had the answer. I was actuallly originally
using sp_spaceused. I executed DBCC UPDATEUSAGE (0) on
the database in question and it corrected the issue.
Thanks for responding so quickly! Bob
>--Original Message--
>Yes, Enterprise Manager is often wrong because it relies
on cached data for
>a lot of its values. If you want a reliable space used
report, use EXEC
>sp_spaceused in Query Analyzer, not Enterprise Manager.
Also check into the
>@.updateusage parameter. (sp_spaceused is well
documented in Books Online).
>Aaron
>
>.
>|||> using sp_spaceused. I executed DBCC UPDATEUSAGE (0) on
> the database in question and it corrected the issue.
Note that if you want to use the EM view, you're going to have to open QA to
execute the DBCC command every time anyway. Why not just use QA, then you
can save a script with EXEC sp_spaceused withthe @.updateusage parameter and
never wonder if your space used report is really up to date.

EMs Database Space Available Wrong

Anyone ever see a case where the Space Available number
reported in Enterprise Manager's General Database
Properties tab is wrong?
My database has the following characteristics:
-1- Has a Daily Backup Maintenance plan (no other
optimizations such as data reorg and such)
-2- Simple Recovery Model
-3- Auto Update Statistics = on
-4- Auto Create Statistics = on
-5- All other settings on Options tab = off
I went and increased the data file to 500 MB larger than
the current size and still do not see Available space
increase. It's sitting at 0.
Any explanations/assistance would be greatly
appreciated. Thanks, BobSee DBCC UPDATEUSAGE in SQL Server 2000 Books Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bob Albert" <bob.albert@.quest.com> wrote in message
news:4d7b01c524de$84e83fc0$a501280a@.phx.gbl...
> Anyone ever see a case where the Space Available number
> reported in Enterprise Manager's General Database
> Properties tab is wrong?
> My database has the following characteristics:
> -1- Has a Daily Backup Maintenance plan (no other
> optimizations such as data reorg and such)
> -2- Simple Recovery Model
> -3- Auto Update Statistics = on
> -4- Auto Create Statistics = on
> -5- All other settings on Options tab = off
> I went and increased the data file to 500 MB larger than
> the current size and still do not see Available space
> increase. It's sitting at 0.
> Any explanations/assistance would be greatly
> appreciated. Thanks, Bob|||That's worked! Thank you so much!
Bob
>--Original Message--
>See DBCC UPDATEUSAGE in SQL Server 2000 Books Online.
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Bob Albert" <bob.albert@.quest.com> wrote in message
>news:4d7b01c524de$84e83fc0$a501280a@.phx.gbl...
>> Anyone ever see a case where the Space Available number
>> reported in Enterprise Manager's General Database
>> Properties tab is wrong?
>> My database has the following characteristics:
>> -1- Has a Daily Backup Maintenance plan (no other
>> optimizations such as data reorg and such)
>> -2- Simple Recovery Model
>> -3- Auto Update Statistics = on
>> -4- Auto Create Statistics = on
>> -5- All other settings on Options tab = off
>> I went and increased the data file to 500 MB larger
than
>> the current size and still do not see Available space
>> increase. It's sitting at 0.
>> Any explanations/assistance would be greatly
>> appreciated. Thanks, Bob
>
>.
>|||> Anyone ever see a case where the Space Available number
> reported in Enterprise Manager's General Database
> Properties tab is wrong?
Yes, Enterprise Manager is often wrong because it relies on cached data for
a lot of its values. If you want a reliable space used report, use EXEC
sp_spaceused in Query Analyzer, not Enterprise Manager. Also check into the
@.updateusage parameter. (sp_spaceused is well documented in Books Online).
Aaron|||Narayana had the answer. I was actuallly originally
using sp_spaceused. I executed DBCC UPDATEUSAGE (0) on
the database in question and it corrected the issue.
Thanks for responding so quickly! Bob
>--Original Message--
>> Anyone ever see a case where the Space Available number
>> reported in Enterprise Manager's General Database
>> Properties tab is wrong?
>Yes, Enterprise Manager is often wrong because it relies
on cached data for
>a lot of its values. If you want a reliable space used
report, use EXEC
>sp_spaceused in Query Analyzer, not Enterprise Manager.
Also check into the
>@.updateusage parameter. (sp_spaceused is well
documented in Books Online).
>Aaron
>
>.
>|||> using sp_spaceused. I executed DBCC UPDATEUSAGE (0) on
> the database in question and it corrected the issue.
Note that if you want to use the EM view, you're going to have to open QA to
execute the DBCC command every time anyway. Why not just use QA, then you
can save a script with EXEC sp_spaceused withthe @.updateusage parameter and
never wonder if your space used report is really up to date.

Empty Report manager page

When I go to http://localhost/Reports I see the page (subscription,
home, ...) but I don't see any tools nor reports.
I have anonymous authentication set to off in IIS for that directory,
and only have integrated authentication turned on. I logged in with
administrator account.
Is there any other way of setting users and roles without using the
'manage reports' page? That way I could make sure administrators has
the correct role.
Thanks in advance,
Stijn Verrept.Are you an administrator on the actual server machine?
Chris
Stijn Verrept wrote:
> When I go to http://localhost/Reports I see the page (subscription,
> home, ...) but I don't see any tools nor reports.
> I have anonymous authentication set to off in IIS for that directory,
> and only have integrated authentication turned on. I logged in with
> administrator account.
> Is there any other way of setting users and roles without using the
> 'manage reports' page? That way I could make sure administrators has
> the correct role.
>
> Thanks in advance,
> Stijn Verrept.|||Chris McGuigan wrote:
> Are you an administrator on the actual server machine?
> Chris
Thanks for the reply, I'm getting desperate :). Yes I am, the server
is a domain controller BTW. I had access earlier but now I don't have
access anymore for some reason, maybe I set something incorrect?|||It does sound like the RS server doesn't recognise you as an
administrator.
I have to say that I did a recent installation on a domain controller
and it went terribly. The main problem seemed to be that the
installation couldn't create the ASPNet user which is essential.
I had to set ASPNet up by hand and had to raise it to administrator
level.
Having said that, your issue seems different. Is it possible that you
changed the role of the administrator in RS so that it didn't have full
rights anymore? If thats a possibility then you will probably have to
reinstall.
Have you had reports running on this installation. If not and you are
trying to set it up for the first time, I would strongly advise you NOT
to put it on a domain controller.
Chris
Stijn Verrept wrote:
> Chris McGuigan wrote:
> > Are you an administrator on the actual server machine?
> >
> > Chris
> Thanks for the reply, I'm getting desperate :). Yes I am, the server
> is a domain controller BTW. I had access earlier but now I don't have
> access anymore for some reason, maybe I set something incorrect?|||Try this url;
http://SERVERNAME/Reports/Pages/SystemSecurity.aspx
If you get access denied, this will prove whether or not you are an
administrator as far as RS is concerned.
Chris
Chris McGuigan wrote:
> It does sound like the RS server doesn't recognise you as an
> administrator.
> I have to say that I did a recent installation on a domain controller
> and it went terribly. The main problem seemed to be that the
> installation couldn't create the ASPNet user which is essential.
> I had to set ASPNet up by hand and had to raise it to administrator
> level.
> Having said that, your issue seems different. Is it possible that you
> changed the role of the administrator in RS so that it didn't have
> full rights anymore? If thats a possibility then you will probably
> have to reinstall.
> Have you had reports running on this installation. If not and you are
> trying to set it up for the first time, I would strongly advise you
> NOT to put it on a domain controller.
> Chris
>
> Stijn Verrept wrote:
> > Chris McGuigan wrote:
> >
> > > Are you an administrator on the actual server machine?
> > >
> > > Chris
> >
> > Thanks for the reply, I'm getting desperate :). Yes I am, the
> > server is a domain controller BTW. I had access earlier but now I
> > don't have access anymore for some reason, maybe I set something
> > incorrect?|||One other thing to consider. Did you enable anonymous access on your
webserver? If anonymous access is enabled then all users are the same (they
are anonymous) and RS does not know you are the administrator. Disable
anonymous access.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:OmiivhMpFHA.272@.TK2MSFTNGP15.phx.gbl...
> It does sound like the RS server doesn't recognise you as an
> administrator.
> I have to say that I did a recent installation on a domain controller
> and it went terribly. The main problem seemed to be that the
> installation couldn't create the ASPNet user which is essential.
> I had to set ASPNet up by hand and had to raise it to administrator
> level.
> Having said that, your issue seems different. Is it possible that you
> changed the role of the administrator in RS so that it didn't have full
> rights anymore? If thats a possibility then you will probably have to
> reinstall.
> Have you had reports running on this installation. If not and you are
> trying to set it up for the first time, I would strongly advise you NOT
> to put it on a domain controller.
> Chris
>
> Stijn Verrept wrote:
>> Chris McGuigan wrote:
>> > Are you an administrator on the actual server machine?
>> >
>> > Chris
>> Thanks for the reply, I'm getting desperate :). Yes I am, the server
>> is a domain controller BTW. I had access earlier but now I don't have
>> access anymore for some reason, maybe I set something incorrect?
>|||I get "You do not have permission to access this page." and I'm logged
in as administrator.|||Bruce L-C [MVP] wrote:
> One other thing to consider. Did you enable anonymous access on your
> webserver? If anonymous access is enabled then all users are the same
> (they are anonymous) and RS does not know you are the administrator.
> Disable anonymous access.
No I only have integrated authentication turned on (mentionned this in
original mail), however, the browser doesn't popup the login screen. I
immediatly get: "You do not have permission to access this page." when
trying to access the page Chris gave me.|||I went back and looked at your original message. You only mentioned the
Reports website, what about Reportserver web. The Reports (report manager)
web is a portal into the reportserver. You need anonymous off for both.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:OMadnSiPYfnzbJjeRVnytA@.scarlet.biz...
> Bruce L-C [MVP] wrote:
>> One other thing to consider. Did you enable anonymous access on your
>> webserver? If anonymous access is enabled then all users are the same
>> (they are anonymous) and RS does not know you are the administrator.
>> Disable anonymous access.
> No I only have integrated authentication turned on (mentionned this in
> original mail), however, the browser doesn't popup the login screen. I
> immediatly get: "You do not have permission to access this page." when
> trying to access the page Chris gave me.|||Bruce L-C [MVP] wrote:
> I went back and looked at your original message. You only mentioned
> the Reports website, what about Reportserver web. The Reports (report
> manager) web is a portal into the reportserver. You need anonymous
> off for both.
YES!!! Good job mate! That did it, nice observing :). I put it to
anonymous so that anyone can look at report.
Thanks a lot!
Stijn Verrept.

Empty Report Manager

The report manager all of the sudden no longer displays any folders or
reports. What can cause this?My guess is something to do with security. If you are a member of the local
administrators group on the server you will see everything without doing
anything. If you are not then you need to setup roles. I create local groups
and then add domain groups/users to the local groups. The other possibility
is the IIS website has been set to allow anonymous access.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:DF980254-4F3B-4B7C-8E4E-FD98EBE1CA14@.microsoft.com...
> The report manager all of the sudden no longer displays any folders or
> reports. What can cause this?

Empty Column ??

I am having some weird issues that I cannot figure out.
- When I right click on a table using Enterprise Manager and return all
rows I see data except that one column is showing nothing. From in
that window I do a IS NULL query verify that there is in fact data in
the column and a LENGTH to verify it's length.
- If I open up the exact same database from within EM and do a select
ont he table it returns all rows and the column is no longer empty but
is showing the correct values.
I am using SA login for both tools. The column datatyype is VARCHAR
(4000), The tools are local and connection to a DB on another server.
There is no padding whatsoever.
Please help me
Ok I did a RTRIM ont he column and now everything is fine, seems there
were some trailing spaces. WHat was weird though was that in my friend
EM he could see the column fine even when I couldn't. Any setting that
would cause this?