Auditing SQL 2008

Auditing SQL Server Password Age

-- Show all logins where the password is over 60 days old
 SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
 FROM sys.sql_logins
 WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE());

-- Show all logins where the password is over 60 days old disregarding specific SQL Server Logins [##MS_PolicyTsqlExecutionLogin##, ##MS_PolicyEventProcessingLogin##]
 SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
 FROM sys.sql_logins
 WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE())
 AND NOT (LEFT([name], 2) = '##' AND RIGHT([name], 2) = '##');

-- Show all logins where the password was changed within the last day
 SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged' FROM sys.sql_logins WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') > DATEADD(dd, -1, GETDATE());
O
R
-- Show all logins where the password is more than 1 month

Select loginname from master..syslogins where datediff(Month,updatedate,getdate()) > 1

reference site: http://www.mssqltips.com/sql_server_dba_tips.asp

Tips to optimize SQL server performance

Top 10 Tips for Optimixing SQL Server

 

10. Facilitate comparisons of workload behavior with benchmarking. What are baselining and benchmarking?

9. Use performance counters to quickly get useful information about currently running operations. Operational Monitoring & Bottleneck Monitoring.
8. Understand why changing server settings usually yields limited returns.
7. Identify performance bottlenecks quickly with DMVs.
6. Learn to use SQL Profiler and traces.
5. See why SANs are more than just I/O.
4. Prevent cursors and other bad T-SQL from returning to haunt applications.
3. Maximize plan reuse for better SQL Server caching.
2. Read the SQL Server buffer cache and how to minimize cache thrashing.
1. Master indexing by learning how indexes are used and how to counteract the characteristics of bad indexes.

read white paper:

http://www.quest.com/Quest_Site_Assets/WhitePapers/Top_10_Tips_for_Optimizing-final.pdf

 

Windows authentication fail in IIS 7

Windows Authentication fails from client machines via hostname/ FQDN or ip address but works on localhost (where the application is published)

Windows authentication supports two authentication protocols, Kerberos and NTLM, which are defined in the <providers> element. When you install and enable Windows authentication on IIS 7, the default protocol is Kerberos. The <windowsAuthentication> element can also contain a useKernelMode attribute that configures whether to use the kernel mode authentication feature that is new to Windows Server 2008.

Windows authentication is best suited for an intranet environment for the following reasons:
 •Client computers and Web servers are in the same domain.
 •Administrators can make sure that every client browser is Internet Explorer 2.0 or later.
 •HTTP proxy connections, which are not supported by NTLM, are not required.
 •Kerberos version 5 requires a connection to Active Directory, which is not feasible in an Internet environment.

The following default <windowsAuthentication> element is configured at the root ApplicationHost.config file in IIS 7.0, and disables Windows authentication by default. It also defines the two Windows authentication providers for IIS 7.0.

<windowsAuthentication enabled="false">
   <providers>
      <add value="Negotiate" />
      <add value="NTLM" />
   </providers>
</windowsAuthentication>

by default for SharePoint - 80  environment only NTLM is required.

Best practice to change SharePoint service account passwords

How to change service accounts and service account passwords in SharePoint Server 2007 (MOSS2007) and in Windows SharePoint Services 3.0

microsoft site reference: http://support.microsoft.com/kb/934838

Just changing domain account password from sharepoint services and from Application Pool identity will not do.
you have to follow the above article.
in short execute the following in batch (.bat) file will also do:
----------------------------------------------------------------------------------------------------------------------------------------------------
cd %commonprogramfiles%\Microsoft Shared\Web server extensions\12\Bin

stsadm -o updatefarmcredentials -userlogin domain\enterusernamehere -password enternewpasswordhere
iisreset /noforce

stsadm -o updatefarmcredentials -userlogin domain\enterusernamehere -password enternewpasswordhere -local
iisreset /noforce
           
stsadm -o updateaccountpassword -userlogin domain\enterusernamehere -password enternewpasswordhere -noadmin
iisreset /noforce
       
stsadm -o spsearch -farmserviceaccount domain\enterusernamehere -farmservicepassword enternewpasswordhere
iisreset /noforce

stsadm.exe -o spsearch -farmcontentaccessaccount domain\enterusernamehere -farmcontentaccesspassword enternewpasswordhere
iisreset /noforce
       
stsadm -o editssp -title (SharedServices1 change accordingly) -ssplogin domain\enterusernamehere -ssppassword enternewpasswordhere
iisreset /noforce
----------------------------------------------------------------------------------------------------------------------------------------------------

InfoPath published Forms error using DNS Record: Event ID 5566, in MOSS 2007 64Bit

InfoPath Form Services Error accessing data source eventid 5566 in SharePoint 2007 

SYMPTOM:

Forms when accessed through the browser using DNS Record (A Record created in DNS: intranet) was showing error. There are no issues when accessing the same forms using hostname(http://intra-app1)

ENVIRONMENT:

SharePoint 2007 SP2 (CU August 2011: 12.0.0.6565), OS: Windows 2008 R2 Enterprise Edn.

SCOPE:

Published Forms opens properly from client access using WFE server hostname (http://intra-app1 ) (Hostname of WFE: intra-app1)

However, the forms could not be accessed using the DNS entry (http://intranet). shoots errors ID 5566 as seen below:  

ACTION:

Inorder to solve this we configured the following setting in the registry for disabling the loopback check in the registry using the following method.

In Registry Editor, locate and then click the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa

Right-click Lsa, point to New, and then click DWORD Value.

Type DisableLoopbackCheck, and then press ENTER.

Right-click DisableLoopbackCheck, and then click Modify.

In the Value data box, type 1, (hexadecimal) and then click OK.

No need to either restart the server or carryout IISreset /noforce

This is enough, just try accessing portal using the DNS and the published forms using DNS record (http://intranet). This should solve the problem.

 

Also, make sure AAM (Alternet Access Maping: http://intranet) to Intranet Zone is configured at the CA level .

Roll Out Plan for Adding iFilters in SharePoint 2007 64Bit Environment

Configure PDF iFilter 9 for 64-bit platform

The steps mentioned below are as per suggested by Microsoft. Please refer to links below for appropriate Microsoft KB articles.

>> http://www.adobe.com/special/acrobat/configuring_pdf_ifilter_for_ms_sharepoint_2007.pdf?PID=4172469
>> http://support.microsoft.com/?id=555209
>> http://blogs.msdn.com/ifilter/archive/2007/03/29/indexing-pdf-documents-with-adobe-reader-v-8-and-moss-2007.aspx

Download Adobe PDF iFilter 9 for 64-bit platforms http://www.adobe.com/support/downloads/detail.jsp?ftpID=4025

1. Install Adobe PDF iFilter 9 for 64-bit platforms.
2. Verify that PDF has been added to the registry.

a. Run Regedit by browsing to c:\Windows\system32\regedt32.exe and double-clicking it.

b. Within left-side tree, browse to: \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\12.0\Search\Applications\{site GUID}\Gather\Portal_Content\Extensions\ExtensionList

c. If PDF extension is present, skip to Step 3 . If PDF extension is not present, continue with Step d.

d. Right click on right-side Extension List pane and choose New > String Value

e. Add a name to the new Registry Key (e.g. "38")

f. Double click the new Registry Key. For "Value data", enter "pdf"

Note: This can also be achieved via SharePoint Server Search Administration page by adding 'pdf' to list of File Types in Search Administration->File Types. This would automatically add an entry for 'pdf' filetype as mentioned above in step 1

3. Verify that PDF has the correct settings in a second registry location.

a. While still in Regedit, within the left-side tree, browse to: \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\12.0\Search\Setup\Filters\.pdf

b. Verify the following values. If values are not as shown, edit them.

i. <REG_SZ> Default = <value not set>
ii. <REG_SZ> Extension = pdf
iii. <REG_DWORD> FileTypeBucket = 1

iv. <REG_SZ> MimeTypes = application/pdf

4. Verify that PDF has the correct settings in a third registry location.

a. While still in RegEdit, within the left-side tree, browse to: \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\12.0\Search\Setup\ContentIndexCommon\Filters\Extension\.pdf

b. Verify the following values. If values are not as shown, edit them.

i. <REG_MULTI_SZ> Default = {E8978DA6-047F-4E3D-9C78-CDBE46041603}

 

5. Verify that pdf.gif is present at the following location:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\IMAGES

6. Add an entry in docicon.xml for the pdf icon:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\XML

<Mapping Key="pdf" Value="pdf.gif">

Note: Step 4 & 5 are done in order to have the pdf icon in SharePoint while it displays the search results.

7. Restart all SharePoint services as well as IIS.

a. Launch the DOS Shell (Start > All Programs > Accessories > Command Prompt).

b. Type the following at the prompt: "net stop osearch". Wait for success message.

c. Type the following at the prompt: "net start osearch". Wait for success message.

d. Type the following at the prompt: "iisreset". Wait for success message.

 Close the Command Prompt window
8. Microsoft Office SharePoint Server can now index PDF files. Also, PDF icon should show in File Types list

Roll Out Plan for PDF Configuration in SharePoint 2007 64Bit Environment

 How to add an icon to Windows SharePoint Services to represent Adobe PDF documents that are stored in document libraries

firstly, download adobe icons (small & large) http://www.adobe.com/misc/linking.html

1. Navigate to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Template\Images
2. Copy in the PDF icon. Rename it to "pdficon.gif".
3. Navigate to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Template\xml
4. Locate the Docicon.xml file open with an xml editor.
5. Within the ByExtension tag add a new Mapping tag in alphabetical order with the following properties.

6. <Mapping Key="pdf" Value="pdficon.gif" />

7. Save the file. The Value needs to be the same as the icon you saved in images. Your file should look something like this.