Knowledgebase : MSSQL Server

If you get the following error, while trying to access MS-SQL server,

"Error: Test connection to the database server has failed because of network problems:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

Follow the steps mentioned below.

1. Login to the server >> Start >> All Programs >> Microsoft SQL Server >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Network Configuration >> Protocols for MSSQLSERVER >> right click “TCP/IP” and select “Enable”.

2. Start >> Run >> type “services.msc” >> Restart SQL service.

Now you can access MS-SQL server.

Connection to a named instance of SQL server 2005/2008 should be made using the format ‘VirtualServer\Instancename’. if the IP address of a machine running a named SQL server instance (ONE)is 192.141.128.140, you can connect to the named instance using  192.141.128.140\ONE for the servername.

To avoid a client app from using the above convention for a servername, a client  alias can be created using the client network utility. Client network utility  can be initiated by

Start->Programs ->Microsoft SQL Server->Client Network Utility

and you can create a Named Pipes or TCP/IP alias for your server and try connecting again.

Windows 2008 server R2 x64, Plesk 9.x,  SQL Server 2008 and R2, Framework 2.xx

When you try to access ASP.NET Application the system shows you an error.

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

SOLUTION:

  • Click START
  • Click ACCESSORIES
  • Right-click COMMAND PROMPT, then click RUN AS ADMINISTRATOR
  • Enter this command: cacls C:\Windows\assembly\GAC_MSIL /E /R psacln /T /C (a list of process will display)
  • Then enter this command: cacls C:\Windows\assembly\GAC_MSIL /E /R psaadm /T /C (a list of process will display)
  • Type Exit and hit enter to close the command prompt.

Restart the Server.

  1. Run SQL Server Configuration Manager.
  2. Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.
  3. Make sure TCP/IP is enabled.

Extra Steps:

  1. Right-click on TCP/IP and select Properties.
  2. Verify that, under IP2, the IP Address is set to the computer's IP address on the local subnet.
  3. Scroll down to IPAll.
  4. Make sure that TCP Dynamic Ports is blank. (Mine was set to some 5-digit port number.)
  5. Make sure that TCP Port is set to 1433.

Note: if you follow these steps, it's not necessary to enable SQL Server Browser, and you only need to allow port 1433, not 1434.

The default instance of the Database Engine listens on port 1433; therefore, you do not have to configure a fixed port. However, named instances including SQL Server 2005 Express Edition listen on dynamic ports. Before you can open a port in the firewall, you must first configure the Database Engine to listen on a specific port known as a fixed port or a static port; otherwise, the Database Engine might listen on a different port each time it is started.

  1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click on the server instance you want to configure.

  2. In the right pane, double-click TCP/IP.

  3. In the TCP/IP Properties dialog box, click the IP Addresses tab.

  4. In the TCP Port box of the IPAll section, type an available port number. For this tutorial, we will use 49172.

  5. Click OK to close the dialog box, and click OK to the warning that the service must be restarted.

  6. In the left pane, click SQL Server Services.

  7. In the right pane, right-click the instance of SQL Server, and then click Restart. When the Database Engine restarts, it will listen on port 49172.

 Port number assignments are managed by the Internet Assigned Numbers Authority and are listed at http://www.iana.org/ Port numbers should be assigned from numbers 49152 through 65535.

After Upgrading to a diffrent version of DotNetNuke, you may get the following error

"Could not load RadEditor. Invalid tools file. Check provider settings in the web.config: ~/Providers/HtmlEditorProviders/Telerik/Config/ToolsDefault.xml"


Solution:

Check the SQL Provider Version in your DotNetNuke Website, to do this navigate to:

>> Providers
>> DataProviders
>> SqlDataProvider

and check the highest version of the "SQLDATAPROVIDER File" type. similar to 05.05.01.SqlDataProvider or 05.05.02.SqlDataProvider.

If your SQLDATAPROVIDER File version is "05.05.02.SqlDataProvider", download the Instllation Pack of DotNetNuke 5.05.02 (Zip File) from DotNetNuke Website.

 

Follow the steps below to install the Telerik Editor Extensions:

  1. UNZIP the DotNetNuke Installation File.
  2. Login to your DotNetNuke website as "HOST" (Super User Account)
  3. In HOST menu click on Extensions, (make sure you're in DNN Edit Mode)
  4. In Extensions Menu, click "Install Extension Wizard"
  5. Click on Browse and select the file > DotNetNuke Instalaltion Files >> \Install\Provider\TelerikEditorProvider_xx_XX_xx_Install file
  6. Click on Next and follow the prompts.

After installation Restart DotNetNuke website from Host > Host Settings.

You receive an error when you Right Click on any table and choose "Edit Top 200 rows"

Error - Invalid prefix or suffix characters. (MS Visual Database Tools)

This is known issue with SQL Server 2008 R2 Management Studio while connecting to SQL Server 2012.There are no claims on what features may/may-not work, and this is expected behavior.

There were errors in basic connectivity and those have been fixed in the past.

For example, http://support.microsoft.com/kb/2459027/EN-US (FIX: "Index was outside the bounds of the array" SMO error message when you connect to a SQL Server code-named 'Denali' CTP1 Database Engine instance in SSMS 2008 R2 and in SSMS 2008)

FIX:

To use all features of SSMS, use the same version as MSSQL Server engine (i.e. MSSQL Server 2012 / MSSQL Server 2012 Management Studio Express)

SYMPTOMS:

When you trry to connect to database servers where you are not in full control over all databases, or where some of your databases are ever offline (or auto-closed), the new version of Management Studio that is shipping with SQL Server 2008 is going to bring you some surprises, when you try to present a list of databases in Object Explorer.

The problem is that for a database that is offline or has been auto-closed, collation comes back as NULL.  Well, that's not really the problem... the real problem is that SSMS throws its hands in the air when it comes across NULL for these values, and assumes this is NULL because you don't have permission.  So it throws up this error:

SQL Error 916

And then refuses to show the data for ANY database, instead of just hiding the one(s) that caused the error.

WORKAROUND:

Databases that are hosted by 3rd party providers, some of which are accustomed to leaving as many databases in auto-close mode as possible,

  1. Highlight the databases node in Object Explorer
  2. Switch to Object Explorer Details
  3. Right-click the column header list on the right (where it says Name, Policy Health State, etc.)
  4. Un-check the Collation option. (For those unaware of Object Explorer Details, go to View > Object Explorer Details, or hit F7.)

At this point, if you refresh the Databases node in Object Explorer, you should again see all of the databases on the server, even those where you don't have access.

You are attempting to install the SQL Server 2008 R2 instance when you receive the following error message: 

"Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes." 

This error can occur if a Microsoft® system folder is either missing or lacking the correct permissions.

Please use the following steps: 

Note: The following information is provided as a convenience. Sage does not provide support for Microsoft Products. For more information on this topic, go to Microsoft.com.
  1. Click the Windows® Start button and click Run.
  2. Type %appdata% and click OK.
  3. Browse to the Microsoft folder.
  4. Locate the Protect folder to assign the correct permissions: 

    Note: If the Protect folder is missing from the Microsoft folder, you will need to create it in this location by right-clicking and selecting Folder. Ensure to name the folder "Protect."
    1. Right-click on the Protect folder and select Properties.
    2. Click on the Security tab. Ensure the System and your logged on Windows User account is listed and both have Full Control.
    3. Click Add to begin adding the accounts. For Windows Vista®/Windows 7, you will need to click Edit first.
    4. In the "Enter the object names to select" box, type the word System, and press Enter on your keyboard. The System account should be listed now. SelectSystem from the user names list and select Full Control under the Allow column. Click Apply .
    5. Repeat these steps (3 and 4) to find, add, and assign Full Control permissions for your logged on Windows user account.
    6. Click OK.
  5. Attempt to install SQLServer 2008 R2.

When you try to perform MSSQL Database backup in DNN4Less Control Panel you receive the following error:

Error: Cannot open user default database. Login failed. Login failed for user 'xxxxxxxxxxxxx'.
Source: .Net SqlClient Data Provider

 Solution :

  1. Login to the Control Panel: https://control.DNN4Less.com/cp
  2. Select your Domain
  3. Go to Websites & Domains 
  4. Click Click Databases
  5. Click Click Database Name
  6. Check the database User Name 
  7. Click Make Default for DB Webadmin

Once you have installed SQL Server Management Studio Express 2008 you can use this tool to connect to, and administer, your MS SQL database.

Before you connect to your database you will need the following details.

  • Database IP address

  • Database username

  • Database password.

 

Step 1
Open SQL Server Management Studio Express 2008. The Connect to Server box will appear. Enter the following:

  • Server type: Select Database Engine from the drop down menu.

  • Server name: Enter the IP address of your database. This information is shown within your DNN4Less control panel.

    • Server Port: Specify the port by appending the database name with a colon (or comma) and the port number e.g., mssql.your-domain.com,14330 . (replace your-domain.com with your actual domain name)

  • Authentication: Select SQL Server Authentication from the drop down menu.

  • Login: Enter your database username. This is the username you chose when you created your database.

  • Password: Enter your database password. This is the password you chose when you created your database.

 
 

Step 2
Click Connect to connect to the database server. A list of all databases on the server will be displayed in the left hand window.

While you will see other databases listed, you will only have permissions to view and administer your own database. Expand the menus to navigate to your database from the list.

 

NOTE:
If your Domain's Name Servers are not set to DNN4Less Name Servers [mssql.your-domain.com,14330] will not work, in this case you can use your website's IP address [184.0.0.111,14330] 


Before this make sure there are free entries by setting the recovery model to simple or backing up the log. 
Enterprise Manager:
Right click on the database, All tasks, Shrink database, Files, Select log file, 
Click OK.
t-sql
dbcc shrinkfile ([db_log_name]) Here [db_log_name] is the logical name of the log file as found from 
sp_helpdb or the table sysfiles
If the log file has grown do to being in full recovery mode then set it to simple before going any further. This should immediately stop the log from growing.
 
Enterprise manager:
Right click on the database, properties, Options, set model to simple, OK.
 
t-sql
sp_dboption [dbname], 'trunc. log on chkpt.', 'true'
If the log file has grown do to being in full recovery mode then set it to simple before going any further. This should immediately stop the log from 
growing.
Enterprise manager:
Right click on the database, properties, Options, set model to simple, OK.
t-sql
sp_dboption [dbname], 'trunc. log on chkpt.', 'true'

The following article explains how to backup your MS SQL Server 2005/2008/2008 R2 database using Micsrosoft SQL Server Management Studio. The backup can then be used at a later date to restore the database if information is lost. The backup file needs to have a .bak file extension.

To backup your MS SQL Server database, please follow these steps:

  1. Open SQL Server Management Studio.
  2. Connect to your MS SQL Server database.
  3. Right-click your database, expand All Tasks and select Back Up.
  4. Under Backup type, select Full.
  5. Under Destination, click Add.
  6. Enter the path and file name for the backup file.
  7. Click OK.
  8. Review the settings for the backup.
  9. Click OK to begin the backup.

The instructions above will create a complete backup of your database. This is the recommended method to ensure that all data is backed up.

You can back up and subsequently restore the databases, database user accounts and data used by domains hosted on your server. You can:

  • Back up your domain databases with all data and user accounts 
  • Restore databases from backup files 
  • Download, upload and remove database backup files 
  • Recover users who became orphaned after the restoration.

To back up a database:

  1. Open up a web browser and navigate to the following URL: https://control.DNN4Less.com/cp
  2. Enter in your username and password that were sent to you when your hosting package was created and click “Sign In” (the username will be the primary contact email address of the hosting package administrator).
  3. Once you have logged into your control panel, Click “Subscriptions /Domains”
  4. Select and click a Subscription
  5. Click “Website & Domains”
  6. Click “Backup Manager”
  7. Click "Database Backup Repository"
  8. Under "Database backup files" \ "Database" drop-down box select your database
  9. Click on Backup icon
  10. In "Backup file name" field enter Database backup name with ".BAK" file extension (databasefilename.bak)
  11. Click "OK"

NOTE: By default "Database Backup Repository" is disabled in DNN4Less Control Panel, to enable "Database Backup Repository" please contact DNN4Less Support, once enabled it will stay enabled.

If the database already exists and you only need to restore its contents:

  1. Go to Domains > domain name > Backup Manager > Database Backup Repository, select the required backup file from the list and click Restore Selected.

    If you do not have the backup file on your server, you can upload the backup file to the server repository from your local machine.

  2. Confirm the restoration by selecting the corresponding check box and click OK.

    If you are restoring MS SQL database, there is a possibility that some database users will be orphaned. In order to provide the ability to access and use the database for these users, you need to repair them.

 

If the database does not exist and you need to recreate it from scratch:

  1. Go to Domains > domain name > Databases and click Add New Database.
  2. Specify the database name, type and server for the database you are restoring.

    Note. You can specify a new database name, but you must choose the correct database type: for example, if you are trying to restore a MySQL database, choose MySQL database type.

  3. Select the Restore database from backup file check box.
  4. Specify the location of the database backup file. You can upload a backup file from your local machine or specify a file located on the server.
  5. Provide the default username and password for the restored database.
  6. Click OK.

To back up a transaction log

1.  After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2.  Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

3.  Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

4.  In the Database list box, verify the database name. You can optionally select a different database from the list.

5.  Verify that the recovery model is either FULL or BULK_LOGGED.

6.  In the Backup type list box, select Transaction Log.

7.  Optionally, you can select Copy Only Backup to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups.

  1. Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.
  2. Optionally, in the Description text box, enter a description of the backup set.

10. Specify when the backup set will expire:

  • To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.
  • The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). To access this dialog box, right-click the server name in Object Explorer and select properties; then select the Database Settings page.
  • To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

11. Choose the type of backup destination by clicking Disk or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.

To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.

12. To view or select the advanced options, click Options in the Select a page pane.

13. Select an Overwrite Media option, by clicking one of the following:

 

  • Back up to the existing media set

For this option, click either Append to the existing backup set or Overwrite all existing backup sets.

Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.

Optionally, enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.

If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.

 

  • Back up to a new media set, and erase all existing backup sets

For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

 

14. In the Reliability section, optionally, check:

  • Verify backup when finished.
  • Perform checksum before writing to media, and, optionally, Continue on checksum error.

 

15. In the Transaction log section:

  • For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.
  • To back up the tail of the log (that is, the active log), check Back up the tail of the log, and leave database in the restoring state.

A tail-log backup is taken after a failure to back up the tail of the log in order to prevent work loss. Back up the active log (a tail-log backup) both after a failure, before beginning to restore the database, or when failing over to a secondary database. Selecting this option is equivalent to specifying the NORECOVERY option in the BACKUP LOG statement of Transact-SQL.

 

16. If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.

 

17. SQL Server 2008 Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.


Source: http://msdn.microsoft.com/en-us/library/ms179478.aspx

To change the “sa” password, login to your computer using an administrator account. Open your SQL Server and log in using Windows Authentication. Open a new query window and type in the following command: 

SP_PASSWORD @NEW = 'new_password', @loginame = 'sa' 

After that go to section Security –> Logins –> double click “sa” or choose properties. Go to “Status” and check to see if “Login:” is enabled. If not, enable it.

Updated: 11-01-2011
If you receive the foloowing error:
Msg 15151, Level 16, State 1, Line 2
Cannot alter the login ‘sa’, because it does not exist or you do not have permission.

Have a look at the follwoing article from Microsoft: http://tinyurl.com/72ayqrn


If you want to change the default settings to allow you to edit more than the 200 rows at a time, or select more than 1000 rows, which are the default settings for Microsoft SQL Server.  To modify “Edit Top 200 Rows” or “Select Top 1000 Rows”  settings do the following:

 

In SQL Server 2008 Management Studio, go to “Tools” -> “Options” -> “SQL Server Object Explorer” -> “Commands“.
Now in the right-hand side “Table and View Options” section, you can change either:
Value for Edit TopRows command, to a value greater than or less than 200.
Value for Select TopRows command, to a value greater than or less than 1000.
By specifying a value of 0, SQL Server will return all rows.  (If you sql tables are really large, you will definitely NOT want to set these values to 0.
Click OK to save your changes.

 If you have a Dedicated or Virtual Server with DNN4Less, Login to your server using (RDP) Remote Desktop Protocol.

  • Click on Start
  • Click Run.
  • Type "Services.msc", press Enter.
  • Look for "SQL Full-text Filter Daemon Launcher".
  • Right-Click on the service and choose "Properties".
  • Set the following properties on it:
    • General: Startup Type = Automatic
    • Log On: Log On As = "Local System Account"
  • Click OK.
  • Restart the MSSQL Server.

If you are a Shared/Cloud Hosting customer of DNN4Less, please submit a ticket to enable the MSSQL Server Full-Text Indexing services for your web site.

Check your web.config file for the following:

connectionString="Data Source=.\\SQLSERVER;Initial Catalog=Northwind_db_;Integrated Security=True"
The problem is "Data Source=.\\SQLSERVER;" - It's the double \\. That's an escape sequence in C#. Switch the connection string to .\SQLSERVER.
If this is not the case please check to make sure that your SQL Server Service is running.
As the error says the particular login is Disabled. Thus, we need to enable the user.

Follow these steps to enable the user.
  1. Go to start -> programs.
  2. Open Sql server management studio
  3. Login to the SQL server (using windows authentication).
  4. Expand the security tab.
  5. Expand the Login folder.
  6. Select the user for which you got this error. In this case 'sa'.
  7. Right click and select properties.
  8. Enable the user.
  9. Save settings.

Often, when restoring a database from a remote machine on my local development machine, my user logins no longer work for that database. The problem lies with the fact that while the user name is the same from machine to machine, the underlying GUID that SQL uses to identify users is different. Thankfully, there is a very easy one line command that fixes these "orphaned" users.


In the context of the database with the orphaned user, run:


EXEC sp_change_users_login 'Auto_Fix, 'username'


Of course, substitute the actual user name for the placeholder in the above script.

You receive an error when you restore a SQL Server database from a Database Backup file (databasename.bak).

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\databasename.mdf'. (Microsoft.SqlServer.Express.Smo)

Note: databasename.mdf is the actual name of your database.

SQL Server is trying to restore the database to '...\MSSQL\databasename.mdf'.'

Change the destination folder to '...\MSSQL\Data\databasename.mdf

Following code always shrinks the Truncated Log File to minimum size possible. Use one of the following two procedures.

PROCEDURE 1

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

 

PROCEDURE 2

  1. Log into Microsoft SQL Server Management Studio
  2. Right click on your database.
  3. Select Properties.
  4. Click on Options.
  5. Change the recovery model to Simple.
  6. Click OK.
  7. Right click on the database.
  8. Select Tasks > Shrink > Database.
  9. Click OK.
  10. Right click on the database.
  11. Select Properties.
  12. Click on Options.
  13. Change the recovery model to Full.

You are recommended to take full back up right before/after above query.

Error:
DotNetNuke.Services.Exceptions.ModuleLoadException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. 

Error Detail:
Error: UsersOnline is currently unavailable.
DotNetNuke.Services.Exceptions.ModuleLoadException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.

Solution:
Make sure the connection string value under appSettings is the same as the connection string under the connectionStrings section.

When you access your MSSQL database through MSSQL Server Eeterprise Manager from Parallels Plesk Control Panel or a web browser you get following error.

"405 - HTTP verb used to access this page is not allowed.
The page you are looking for cannot be displayed because an invalid method (HTTP verb) was used to attempt access."

Solution:

1. Enter into %plesk_bin% directory.
2. Execute the command 'defpackagemng --fix --type=sqladminmssql '

 

If above solution does not work change the application pool for 'mssql' to 'DefaultAppPool' that is set to 'integrated' mode. And then try the above solution again.