Error Scripting database with only dbo rights

With only dbo permissions to a database, you will get this error when you try to script the database.

"Could not read metadata, possibly due to insufficient access rights. (Microsoft.SqlServer.Smo)"

Try this:

1. From SSMS
2. Right click on the server name and click properties
3. Click on the Permissions tab
4. Click the user name
5. On the Explicit Permissions almost at the bottom there is "View Any Definition" grant that.

Reference:
http://www.sqlservercentral.com/Forums/Topic355850-149-1.aspx

Allowing a DBO to manage SSIS packages (SQL 2008)

We have developers that manage their own application's databases (dbo) and SSIS jobs.  In order to give them "least priv" to SQL 2008, this is what I've been doing to give them rights to manage their database(s) and their SSIS jobs on SQL server.

• Add user admin account to local "Distributed COM Users" group
• Give "Distributed COM Users" group remote access to the DCOM config for SQL; see http://systems808.blogspot.com/2010/07/access-denied-to-ssis-remotely.html
• Give user admin account access to msdb database with the following rights:
• DatabaseMailUserRole
• db_ssisltduser
• public (default)
• SQLAgentOperatorRole
• SQLAgentReaderRole
• SQLAgentUserRole

Hope that helps!


For SQL 2012+, see:
https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service?view=sql-server-2016

syspolicy_purge_history job failing

Basically, had to fix powershell command line to use the cluster virtual server name for each instance vs the server name...

Reference:
http://support.microsoft.com/kb/955726

Reverse lookup of AD SID

Netapp's data ONTAP OS has this nice command...

cifs lookup textual_sid_S-x-y-z

The cifs lookup command translates a Windows NT user or group name into its corresponding textual Windows NT SID (Security ID), or a textual NT SID into its corresponding Windows NT user or group name.
domain\name is the name of an account in a specified Windows domain. If the domain is omitted, then the name is looked up in the domain in which the Filer is a member server. Conversely, given a Windows Security ID (SID), cifs lookup will return the corresponding account name.

Reference:
http://www.wafl.co.uk/cifs_lookup/

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’

How to Fix this?

1.Map the User to MASTER Database( By Def. It will be added to Public Role)

2.USE MASTER

GO

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO LOGIN

 

Reference:

http://mssqltrek.com/2011/12/05/the-execute-permission-was-denied-on-the-object-xp_prop_oledb_provider-database-mssqlsystemresource/

 

 

Netbackup jobs failing with 48 status code

To resolve, check the hostname resolution from media server to client and vice versa.

Try below from media to client and client to media:

• Bpclntcmd -hn clientname
• Bpclntcmd -ip client ip
• Bptestbpcd -client clientname

If there is name resolution issue, resolve it.  Ie. put entries in HOSTS file, etc...

If changes have been made recently, remember to clear NBU host cache - even on client:

• bpclntcmd -clear_host_cache
• also clear ipconfig /flushdns

SQL 2008 Cluster installation fixes

Spent all day working on trying get one named instance of SQL 2008 installed on a Win 2008 R2 cluster.

Anyway, these are the things I encountered:

• SQL server 2008 failover cluster 'RequireKerberos' error;  The fix was to slipstream SQL 2008 SP1 into the installation process
• SQL cluster resource would not startup at end of installation; Name resource could not come online.  The fix was to give cluster computer name FULL CONTROL of the virtual instance names in Active Directory using ADUC.
• Verification check prior to installation would fail and not allow me to install SQL.   After manual verification that things should be ok, I used this command "setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster /PCUSource=c:\SP1" to finally start up the installation process.

References:
http://trilist.blogspot.com/2010/02/fix-sql-server-2008-failover-cluster.html

Controlling issuance of RDS CALs

Problem:
Running out of RDP licensing for your terminal servers.  Win 2008 & Win 2008 R2 licenses.  You need to restrict/control what servers are allowed to get RDP licenses from the your pool of licenses. 

Solution:
On the license server, there is a local security group called Terminal Server Computers.  Add the client servers (computer names) that are allowed to get RDP licensing for their clients to the security group.  Once all the servers are in the group, enable policy "License server security group" found in Computer Configuration\Policies\Administrative Templates\Windows Components\Remote Desktop Services\RD Licensing using GPEDIT.MSC. 

Caveat:
This solution should only be done when Licensing server is a domain member.

Reference:
http://technet.microsoft.com/en-us/library/cc725704.aspx

Configuring multiple SSL DNS URLs for same IP/port in IIS 7.5

Basically, you have a website that has to be SSL enabled, but different URLs for different groups of users.  Ie. internal company users might use www.abc.edu vs external might use www.abc.com, or whatever. 

To do this, you need a SAN Certificate (Subject Alternative Name Certificate).  A SAN cert allows for multiple domain names to be protected with a single certificate.

The SOP to generate it is below in the reference.  Send the certreq file to your preferred CA (Certificate Authority) and they will send back a SAN cert.  Bind that new cert to your SSL binding for the website and either any and all URLs in the SAN cert will work as a SSL link.

Adding another DNS to the SSL, would require a new SAN cert be generated with all the valid DNS' listed, including the new one.

Reference:
http://blogs.msdn.com/b/andrekl/archive/2008/09/24/how-to-generate-a-csr-for-an-iis-website-using-the-windows-vista-server-2008-certificates-mmc-plugin.aspx

http://blogs.msdn.com/b/varunm/archive/2013/06/18/bind-multiple-sites-on-same-ip-address-and-port-in-ssl.aspx

How to remove cluster warning: ESXi Shell for the Host has been enabled

When taking Vmware ESXi hosts out of maintenance mode, I used to see this all the time.  You can ignore if you know that you have the shell enabled, but found this method to suppress the warning...

Reference:
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2003637

Migrate Reporting Services to SQL 2008 RS

Here is the MS SOP, works well.

Reference:
http://msdn.microsoft.com/en-us/library/ms143724(v=sql.100).aspx

Blank page for SQL 2008 Reporting Services management site

Finally figured this out.  The Windows 2008 R2 image we use had the described setting enabled by default.  Had to disable in order for the Reporting services management page to work/display.

 

*** Report Manager fails after you enable FIPS compliant algorithms in Local Security Policy

After you enable the Local Security Policy "System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing", you are not able to use Report Manager. When you open Report Manager, your browser will be empty or show an "HTTP 500 Internal Server Error". A SQL Server 2008 R2 or later version of the report server will add an error message similar to the following in the report server service log file:

ui!ReportManager_0-1!1708!02/27/2010-08:02:03:: e ERROR: System.InvalidOperationException: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms.

To work around this issue: At this time, there is no known work around. Report Manager does not support the Local Security Policy "System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing". If you want to use Report Manager you need to disable the setting and restart the report server service.

This issue applies to: SQL Server 2008 Reporting Services and SQL Server 2008 R2 Reporting Services.

Reference:

http://technet.microsoft.com/en-us/library/ms345220(v=sql.105).aspx