Make sure the SQL Authentication is set to ‘SQL Server and Windows Authentication Mode’
Using SQL Server Management Studio
To change security authentication mode
- In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
- On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
- In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
- 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
- In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
- On the General page, you might have to create and confirm a password for the login.
- 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)
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.
- Create a domain account that has access only to computers and servers that provide data or services to a report server.
- Open a command prompt: On the Start menu, click Run, type cmd in the text box, and then click OK.
- Type the following command to configure the account:rsconfig –e –m<computername> –s<sqlservername> –u<domain/username> –p<password>
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
– 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.
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
<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”/>
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
You could query this registry value to get the Instance Name.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names