Microsoft Sql Server Administration…

Useful commands I’ve found from various sources for Microsoft Sql Server 2005/2008 include:

EXEC sp_configure
GO
–Instance level principals.
SELECT * FROM sys.asymmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.credentials
SELECT * FROM sys.linked_logins
SELECT * FROM sys.remote_logins
SELECT * FROM sys.server_principals
SELECT * FROM sys.server_role_members
SELECT * FROM sys.sql_logins
SELECT * FROM sys.endpoints
GO

–Database level principals.
SELECT * FROM sys.database_principals
SELECT * FROM sys.database_role_members
GO
ALTER LOGIN sa WITH NAME = hiddenSaAccount
GO
–Check your user execution context
SELECT SUSER_SNAME(), USER_NAME()
GO
–View the list of objects in the database
SELECT * FROM sys.objects
GO
–Change user context
EXECUTE AS USER = ‘AnotherUserName’
GO
–Return to your login
REVERT
GO
–explore database files
select * from AdventureWorks.sys.database_files
select * from master.sys.database_files
select * from msdb.sys.database_files
select * from tempdb.sys.database_files
–explore endpoints
select * from sys.endpoints
select * from sys.tcp_endpoints
select * from sys.http_endpoints
select * from sys.database_mirroring_endpoints
select * from sys.service_broker_endpoints

–Security

SELECT * FROM sys.symmetric_keys
GO

SELECT * FROM sys.certificates
GO

Error: Connect to SSIS Service on machine “” failed:

On a new instalaltion of SQL Server 2008, running on Windows Server 2008, l cannot connect to SSIS from SSMS (Sql server management console) 2008 running on a client workstation. I can connect from the local server end.
I tried adding the users Group to the permissions option on Component Services/MsdtsServer100 as suggested by many sites since this problem is common also in SSIS/Sql Server 2005. This didnt work.
Solution was to add the user group to the Windows Server Distributed COM Users group.
See also: Technet link

Render SSRS 2008 Report from Website to Adobe PDF

This is a test project to see if l can successfully render to PDF from an asp.net website, using Report Control from a Visual Studio 2008 project.
First l created a simple sample table called MyContact from some files from the AdventureWorks database. Heres the code:

SELECT
    [ContactID]      ,[Title]      ,[FirstName]      ,[LastName]
      ,[EmailAddress]      ,[Phone]      ,[ModifiedDate]
      into MyContacts
  FROM [AdventureWorks].[Person].[Contact]
GO

If you wish to create the table directly, heres the code:

USE [AdventureWorks]
GO
/****** Object:  Table [dbo].[MyContacts]    Script Date: 01/15/2009 22:34:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyContacts](
 [ContactID] [int] IDENTITY(1,1) NOT NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [dbo].[Name] NOT NULL,
 [LastName] [dbo].[Name] NOT NULL,
 [EmailAddress] [nvarchar](50) NULL,
 [Phone] [dbo].[Phone] NULL,
 [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

I’ll attach a link to a text file with data for populating this with over 19,000 rows, as well as a link to the entire source code.
Heres a link to the Report Webpage. (should be active soon)
Currently l’m trying to debug the error below:

Configuration Error

Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load file or assembly ‘Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The system cannot find the file specified.

Source Error:

Line 53:     <add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
Line 54:     <add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
Line 55:     <add assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
Line 56:     <add assembly="Microsoft.ReportViewer.Common, Version=9.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
Line 57:    </assemblies>

 

 

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLMSoftwareMicrosoftFusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLMSoftwareMicrosoftFusion!EnableLog].

This seems to be a possible solution to this problem.

Unable to connect to remote server – error

If you get this message say from Visual Studio when trying to deploy SSRS reports to a SQL Server 2008 database instance, heres how this can be resolved. This is usually caused by kerberos security authentication. Navigate to: C:Program FilesMicrosoft SQL ServerMSRS10.MSSQLSERVERReporting ServicesReportServer Edit the file rsreportserver.config
Make the change below:
<!–<Add Key=”SecureConnectionLevel” Value=”2″/>–>
<
Add Key=SecureConnectionLevel Value=0/>

 Restart Reportserver. You should be able to deploy your reports now to SQL Server Reporting Services 2008.

Windows Tech Notes

Virtual PC 2007

Using Microsoft Virtual PC 2007, selecting other allows you to install Windows 2008 Server as l have successfully done on a Microsoft Vista Ultimate workstation and a Windows XP workstation. To add the essential Virtual Machine Additons under the action menu, make sure you are logged into the Virtual Operating System. After this, you can now go to the Edit menu/Settings and add say Shared Folders.

Installing SQL Server 2008

On server configuration, when you get this error message ” Must provide AGTSVCACCOUNT parameter” – heres what worked for me. Set sql server agent, sql server (MSSQL), sql server analysis, SSRS, SSIS with blank passwords, but NT_AUTHORITYNETWORK SERVICE as username. For SQL Server Browser – it defaults to NT AUTHORITYLOCAL SERVICE.

Windows Server 2008

Heres the error l get trying to log into my Windows 2008 server remotely.

” To log on to this remote computer, you must be granted the allow log on through terminal services right. By default, members of the remote desktop users group have this right. If you are not a member of the remote desktop users group or another group that has this right, or if the remote desktop user group does not have this right, you must be granted this right manually”

To get Remote Desktop to work, add the user as a menber of the Remote Desktop users group AND also, go to the local security policy, Local policies branch, right click on Allow log on through terminal services properties,  and add the Remote Desktop Users group.  You do not need to install Terminal Services Server to get this to work.