Category Archives: Microsoft SQL

change the password of the SQL sa account

By

Make sure the SQL Authentication is set to ‘SQL Server and Windows Authentication Mode’

Using SQL Server Management Studio

To change security authentication mode

  1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
  3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
  4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login

  1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
  2. On the General page, you might have to create and confirm a password for the login.
  3. On the Status page, in the Login section, click Enabled, and then click OK.

Change the password

  • Click on Security – Logins
  • Right Click on the SA Account
  • Click on Properties
  • Change the password and click OK

Restart the following SQL services and restart the server if needed

  • SQL Server (MSSQLSERVER)
  • SQL Server Agent (MSSQLSERVER)

Configuring an Account for Unattended Report Processing

By

You can configure a special account to use for unattended report processing (that is, to support subscriptions, scheduled report history generation, and scheduled updates to a report execution snapshot). This account is used for special-case reports that do not use credentials, which is an uncommon scenario for most reports.

To specify the account, use the -e argument of rsconfig. Specifying the -e argument for rsconfig directs the utility to write the account information to the configuration file. You do not need to specify a path to rsreportserver.config. Follow these steps to configure the account.

  1. Create a domain account that has access only to computers and servers that provide data or services to a report server.
  2. Open a command prompt: On the Start menu, click Run, type cmd in the text box, and then click OK.
  3. Type the following command to configure the account:rsconfig –e –m<computername> –s<sqlservername> –u<domain/username> –p<password>

http://msdn.microsoft.com/en-us/library/aa972235(v=sql.80).aspx

 

Reporting Services 2008 – The report server installation is not initialized

By

If you get an error similar to the one below when trying to setup the report server.

The application domain WindowsService_1 failed to initialize. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized. at Microsoft.ReportingServices.Library.ConnectionManager.b_2() at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.<>c_DisplayClass1.b__0(Object state) at System.Security.SecurityContext.Run(SecurityContext securityContext, ContextCallback callback, Object state) at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.Run(ContextBody callback) at Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey() at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage() at Microsoft.ReportingServices.Library.ServiceController.ServiceStartThread(Object firstStart).

Then try deleting the Encryption Keys by following the steps below:

– Click on Start – All Programs – Microsoft SQL Server 2008 R2 – Configuration Tools – Reporting Services Configuration Manager
– Click on Connect
– Click on Encryption Keys
– Click on Delete

Check the article below to find more information about deleting / re-creating the encryption keys
http://msdn.microsoft.com/en-us/library/ms156010.aspx

 

SQL SERVER – FIX : ERROR : Cannot open database requested by the login. The login failed. Login failed for user ‘NT AUTHORITY\NETWORK SERVICE’

By

Solution:

– Go to SQL Server Security Logins and right click on NT AUTHORITY\NETWORK SERVICE and select Properties

In newly opened screen of Login Properties, go to the “User Mapping” tab. Then, on the “User Mapping” tab, select the desired database – especially the database for which this error message is displayed. On the lower screen, check the role db_owner. Click OK.

A network-related or instance-specific error occurred while establishing a connection to SQL 2008 Server…

By

1. Make sure your database engine is configured to accept remote connections
• Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration.
• Click on Surface Area Configuration for Services and Connections.
• Select the instance that is having a problem > Database Engine > Remote Connections.
• Enable local and remote connections.
• Restart instance.

2. Check the SQL Server service account
• If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding.

3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings in your ASweb P.NET application.
• Usually the format needed to specify the database server is machinename\instancename
• Check your connection string as well

<connectionStrings>
<add name=”SampleConnectionString” connectionString=”Data Source=machinename\instancename;Initial Catalog=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30″ providerName=”System.Data.SqlClient”/>
</connectionStrings>

4.You may need to create an exception on the firewall for the SQL Server instance and port you are using.
• Start > Run > Firewall.cpl
• Click on exceptions tab
• Add the sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Binn), and port (default is 1433)
• Check your connection string as well

5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser.

7. Check that you have connectivity to the SQL Server. Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver
• Start > Run > cmd
•netstat -ano| findstr 1433
•telnet myserver 1433
•ping -a myserver

UPGRADE SQL SERVER EXPRESS INSTANCE TO SQL SERVER ENTERPRISE

By
  • – In the Start menu, under Microsoft SQL Server 2008 R2, open the SQL Server Installation Center in the Configuration Tools folder.
  • – Under Installation, click Upgrade from SQL Server 2000, SQL Server 2005 or SQL Server 2008.
  • – You may be prompted to browse for the SQL Server 2008 R2 installation media to install the Setup Support files. In my case, I mounted the ISO.
  • Make sure the Setup Support Rules pass before continuing on.
  • Run the install
  • After the install is complete, you can now continue with the process to upgrade the Express instance.
  • Again, make sure the Setup Support Rules all pass before continuing on.
  • The setup should find your existing product key. If not, enter it now.
  • Accept the license terms
  • Select the SQL instance to upgrade. In my case, the name of the Express instance was “SHAREPOINT”.
  • The installer will preselect the features to be upgraded in the instance.
  • Verify the Instance ID and root directory, and continue on.
  • Verify you have enough drive space, and click Next.
  • Decide if you want to turn on error reporting, and click Next.
  • Verify the Upgrade Rules all pass before continuing on.
  • The installer will provide a summary of the upgrade actions. Click Upgrade.
  • After the installer finishing the upgrade, it will provide a confirmation page that the instance was successfully upgraded.
  • When reviewing the properties of the SQL instance in the SQL Server Management Studio, it will now show that it has been upgraded to SQL Server Enterprise Edition.