TechNet Blogs > Microsoft SQL Server

Microsoft SQL Server

SQL Server Core Engineer Tips
Common Tasks
Search

SQL globe readers

SQL Locations of visitors to this page


Recent Posts
Tags
Archives
Archives
Sort by: Most Recent | Most Views | Most Comments
Excerpt View | Full Post View
  • Microsoft SQL Server

    Use a DNS alias for SSAS 2005/2008 instance

    Posted over 2 years ago
    by

    You would like to configure a DNS alias (CName) for each server to  redirect transparently clients for high availability.

    There is no indication against to use a DNS alias, you can use CNAME to connect to SSAS 2005 or greater. However there are some considerations to take into account.

    1. Be carefully on cluster system

    2. Be sure that you can “reverse lookup” the CNAME / Alias. SSAS has to reverse lookup the hostname during connection 

    3.  Changing the DNS Alias: DNS caching might cause some delay
    - Client side cache: It can be cleared with ipconfig /flushdns
    - Server Side cache : Depends on the structure, by multilevel structure multiple caches might exist Clearcache possible with dnscmd or GUI (there are settings for the cache lifetime but it is not recommended to change them (it might increase the traffic on the DNS server))

    4. Cannot connect locally to your SSAS instance using DNS alias

    5.  As the alias uses only server name you cannot configure the protocol , the name of the instance or the port.

    6.  At kerberos level you can have false SPN. The SPN needs to be re-registered on the alias name too.

    7.  At TCP level you can have errors

    8.  DNS will no provide an port information to clients.  If Default instance is listening on a non-default port (SQL Browser cannot redirect) , then port has to be defined in the connection string.


    You can create a DNS alias following this article kb: How to set up application names in SQL Server 2000 Analysis Services


    Connect bug: Create and alias to a (named) instance of Analysis Services 2005/2008

     

    Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

  • Microsoft SQL Server

    Cannot connect locally to your SSAS instance using DNS alias

    Posted over 2 years ago
    by

    Problem: You are not able to connect to your SSAS instance. You got the error message below:

    Cannot connect to ServerName\InstanceName.
    A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)
    Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
    An existing connection was forcibly closed by the remote host (System)

    This issue occurs with SSAS 2005 or 2008 on Windows 2003 SP2 or Windows 2008.

    Background: You have defined a DNS alias for your Analysis Services instance.


    Work done:     In SQL Server Management Studio, the connection works fine when :
     - connecting to this SSAS instance remotely with DNSalias\Instance or machineName\InstanceName.
     - connecting to this SSAS instance by replacing DSNAlias by local

    The connection doesn't work only in local when server name is DSNAlias\InstanceName

    Cause: A loopback check security feature that is designed to help prevent reflection attacks on your computer. Therefore, authentication fails if the FQDN or the custom host header that you use does not match the local computer name.


    Workaround: Execute your command remotely or in local with server name like .\instanceName.

    There is another  workaround extracts from the article kb http://support.microsoft.com/kb/983444 . The method is to disable the loopback check by setting the DisableLoopbackCheck registry entry. But it's not recommanded. This workaround may make a computer or a network more vulnerable to attack by malicious users or by malicious software such as viruses. Use this workaround at your own risk.

     

    See also : Use a DNS alias for SSAS 2005/2008 instance

    Michel Degremont | Microsoft Premier Field Engineer - SQL Server Core Engineer |


     

  • Microsoft SQL Server

    Can I install SQL Server on a domain controller?

    Posted over 2 years ago
    by



    For security and performance reasons, we recommend that you do not install a standalone SQL Server on a domain controller.

    Regarding  failover cluster, SQL instances are not supported where cluster nodes are domain controllers.

    Other point, a read-only domain controller (RODC) is a new type of domain controller in the Windows 2008. SQL Server is not supported on a read-only domain controller.

    Current resolution on Promotion and Demotion of Domain Controllers is to not support it. If the server is already a domain controller, SQL can be installed.  But once SQL has been installed user will  not be permitted to change the DC to a member machine.


    BOL : Installing SQL Server on a Domain Controller 2008
    • You cannot run SQL Server services on a domain controller under a local service account or a network service account.
    • After SQL Server is installed on a computer, you cannot change the computer from a domain member to a domain controller. You must uninstall SQL Server before you change the host computer to a domain controller.
    • After SQL Server is installed on a computer, you cannot change the computer from a domain controller to a domain member. You must uninstall SQL Server before you change the host computer to a domain member.

     
    For more information:
    - Installing SQL Server on a Domain Controller 
    - You may encounter problems when installing SQL Server on a domain controller 
    - Error message when you install SQL Server 2005 on a Windows Server 2008-based RODC: "Failure creating local group SQLServer2005SQLBrowserUser$<ServerName>"
    - Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster


    For the SQL Engine, Analysis Services, SQL Browser, you should also check the SPN's in the active directory.
     

     

    Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

  • Microsoft SQL Server

    Can I move SQL Server to another domain?

    Posted over 2 years ago
    by

    If your installation is a stand-alone instance, complete your Users/Computer migration through "Active Directory Migration Tool" and put the SQL box in the new domain. You have also to change your job owners, proxy accounts, and service accounts to the new domain as well. If you continue to use accounts from the old domain make sure the trust works and that the access to the other domain's DCs is fast. Don't forget to rename the integrated login names when the windows domain name has been changed by using ALTER LOGIN [login name] with name=[new name]

    If it is a cluster then you will have a problem with the domain groups for the clustered services. After you install a SQL Server failover cluster, you can change the service accounts. However, you cannot change the domain groups. If you want to use different domain groups, you can uninstall and then reinstall SQL Server. Domain migration for SQL Server 2008 Failover Cluster instance is also not supported. You must reinstall the SQL Server Failover instance.

    Regarding SQL Server 2005, you can also follow method 2 in 915846

     

    For more information:
    - You must specify the domain groups for the clustered SQL Server services when you install a SQL Server failover cluster
    - Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster
    - "No mapping between account names and security IDs was done" error when adding a node to a SQL Server 2008 Failover Cluster

     

    Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

  • Microsoft SQL Server

    Resolve SQL Server connectivity issues

    Posted over 2 years ago
    by
    • 0 Comments

     


    In this post, I am sharing various tests that should help you debug the problem. The steps below you help you to fix various issues like error message below:


    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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: xxx)

    SERVER SIDE

    Step 1: SQL Server Configuration Manager
    Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if  :
    - SQL Server service status is “Running”.
    - SQL Browser service status is “Running”. (if you have got an instance that is not by default with port 1433)
    - Check if TCP/IP and NP are enabling

    Step 2: Firewall
    Disable your Firewall and check if the issue doesn't occur follow the both articles below

      How do I open the firewall port for SQL Server on Windows Server 2008
      Configuring the Windows Firewall to Allow SQL Server Access

    Step 3 : Enable Remote Connection
    Right click on the server node and select Properties. Go to Left Tab of Connections and check “Allow remote connections to this server”

      To configure the Database Engine to accept remote connections


    CLIENT SIDE

    Step 1 : Connection string is not correct or point to not a real server. Be ensure of the instance name

    Step 2 : Check if the port is opened
    telnet <your_target_machine> <TCP_Port>

    Step 3 : Ping server
    ping -a <your_target_machine>   
    ping -a <Your_remote_IPAddress>
    Run “net view \\your_target_machine”

    Step 4 : SQL Alias.
    On my end I was working on a 64 bit machine but had to remove an alias under the SQL Configuration Manager -> 32 Bit Config -> Alias.
    Also confirm with cliconfg.exe

    The registry keys are: 
    32 bit:  HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
    64 bit:  HKLM\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo
     
    Step 5 : Try the connection out of your application
    SQLCMD –E -S tcp:<IPAddress>[\<InstanceName>],<Port>
    SQLCMD –E -S tcp:<Your_target_machine>[\<InstanceName>],<Port>

    Try the FQDN to connect to the server that is running Microsoft SQL Server.
    SQLCMD –E -S tcp:<Your_target_machine>.<FQDN>[\<InstanceName>],<Port>


    Others relevant documentations

      SQL_Protocols Team 
      How to configure SQL Server 2005 to allow remote connections
      How to configure an instance of SQL Server to listen on a specific TCP port or a dynamic port
      Troubleshooting Server and Database Connection Problems
      SQL Server 2005 Connectivity Issue Troubleshoot - Part I
      Troubleshoot Connectivity Issue in SQL Server 2005 - Part II
      Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
      Steps to troubleshoot SQL connectivity issues
      Named Pipes Provider, error: 40 - Could not open a connection to SQL Server (Microsoft SQL Server, Error: XXX)

     

    Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

     

  • Microsoft SQL Server

    Access to the remote server is denied because no login-mapping exists.

    Posted over 2 years ago
    by

        

    Problem: Unable to run a query through a linked server SQL Server 2005. This problem only happens with a non-sysadmin account.

    You got the message below:

    Msg 7416, Level 16, State 2, Line 1
    Access to the remote server is denied because no login-mapping exists..


    Cause: When creating a linked server with the parameter @provstr and you use a local SQL Server non-admin or non-Windows account, you have to add the parameter "User Name"   into the @provstr


    Resolution : Add "User ID=Username" into the provider string on your linked server

    EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server', @provstr=N'SERVER=serverName\InstanceName;User ID=myUser'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName', @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser', @rmtpassword = N'*****'

    Check:

    SELECT  TOP 1 * FROM LinkServerName.msdb.dbo.backupset
    GO
    SELECT * FROM OPENQUERY (LinkServerName, 'SELECT TOP 1 * FROM msdb.dbo.backupset ')

    Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

  • Microsoft SQL Server

    SQL Server System Views Map

    Posted over 2 years ago
    by

    The Microsoft SQL Server System Views Map shows the key system views included in SQL Server, and the relationships between them :

    - SQL Server 2005 System Views Map 
    - SQL Server 2008 System Views Map
    - SQL Server 2008 R2 System Views Map

    - Mapping System Tables to System Views (Transact-SQL)

     

    Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

  • Microsoft SQL Server

    SQL Server 2008 connectivity issue : cannot generate SSPI context

    Posted over 2 years ago
    by

        

    Problem description: We were not able to make a connection on SQL Server 2008 from a remote server with Windows account. We got the error message: cannot generate SSPI context:


    9649 A security (SSPI) error occurred when connecting to another service broker: . Check the Windows Event Log for more information.
    ...
    11248 A corrupted message has been received. The SSPI login header is invalid.
    ...
    17806 SSPI handshake failed with error code X, state %d while establishing a connection with integrated security; the connection has been closed. 
    ...
    Connection handshake failed.

     
    Work done: We followed the troubleshooting step below:

    Step 1:  made a  TELNET on machine port and confirmed that the port of SQL Server instance was open

    Step 2:  We checked if the SPN for my instance SQL server exist with command below:

    Start >> Run >> CMD >> Setspn -L <YourSQLServiceAccount>

    Registered ServicePrincipalNames for CN=svc-mssql-sgbd,OU=Users,OU=_Paris,DC=puteaux,DC=net:
            MSSQLSvc/serverName:PORT
            MSSQLSvc/serverName


    Step 3: From my remote server, I used an UDL file to make a connection.

    Click right on your desktop >> new file >> then rename your text file by myTest.UDL.


     
    Double click on it. Click on the tab PROVIDER. And select SQL Server Native client 10.0

         I tried to make a connection with another protocol, like Name Pipe.
         Np:<ServerName>\<IntanceName>  
              Works fine

         Then I tried with TCP protocol with SQL authentication 
         tcp:<ServerName>\<IntanceName>
               Works fine

          Then I tried with TCP protocol with Windows authentication 
         tcp:<ServerName>\<IntanceName>  
              Failed with error: cannot generate SSPI context

         Then I tried with TCP protocol with Windows authentication and the port
         tcp:<ServerName>\<IntanceName>,PortNumberOfYourSQLInstance  
              Failed with error: cannot generate SSPI context

         Then I tried with TCP protocol with another account Windows authentication 
         tcp:<ServerName>\<IntanceName>
               Works fine

     

      

     

     

    Cause: we enabled the verbose logging to Kerberos following the article Microsoft KB: http://support.microsoft.com/kb/262177 .

    And we found out inside Windows system event the error message:

    The kerberos SSPI package generated an output token of size 12536 bytes, which was too large to fit in the token buffer of size 12535 bytes, provided by process id 4.
    The output SSPI token being too large is probably the result of the user     myUser@myDomain.com  being a member of a large number of groups.
    It is recommended to minimize the number of groups a user belongs to. If the problem can not be corrected by reduction of the group memberships of this user, please contact your system administrator to increase the maximum token size, which in term is configured machine-wide via the following registry value: HKLM\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters\MaxTokenSize.

     

     

    Resolution: We increased the maximum token size following the article KB to get back in production : http://support.microsoft.com/kb/327825 ,  then customer investigates to see why this user being a member of a large number of groups.

     

     

    Related Resources:

         The SQL Network Interface library was unable to register SPN

         How to troubleshoot the "Cannot generate SSPI context" error message

         “Cannot Generate SSPI Context” error message, more comments for SQL Server

    Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

  • Microsoft SQL Server

    The database principal owns a schema in the database, and cannot be dropped

    Posted over 3 years ago
    by

      


    Description of the problem: When you tried to drop a user, you got this message:

    Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

    Cause: That means, you are trying to drop a user owning a schema. In order to drop the user, you have to find the schema that’s assigned and then transfer the ownership to another user/role or to drop it.

    Resolution: You can fix the issue following two ways.


    By script: You can find out which schema is owned by this user with the query below:

    SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('myUser')

    Then, use the names found from the above query below in place of the SchemaName below. And drop your user.
       
    ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo
    GO
    DROP     USER myUser

    By Management Studio:

    - Object Explorer >> Expand the [databasename] >> Security.
    - Click on Schemas.
    - In summary window, determine which Schema(s) are owned by the user and either change the owner or remove the Scheme(s).
    - If they are system schema(s), I suggest to change them to ‘dbo’.
    - Drop your user.

    More detail about schemas into the BOL:
    http://msdn2.microsoft.com/en-us/library/ms190387.aspx

     

    Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

  • Microsoft SQL Server

    SQL 2008 Unattended Installation failed with error illegal characters in path

    Posted over 3 years ago
    by

      


    Description of the problem: Last week, I was with my customer performing multiple unattended installations of SQL Server 2008:  Setup.exe /q /configurationfile=c:\myconfiguationfile.ini

    In myconfiguationfile.ini, the switch INSTANCEDIR is set to "E:\" like INSTANCEDIR="E:\"

    But the setup was fail with the message: "Illegal characters in path" like below in details.txt:

    Slp: Exception type: Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException
    Slp:     Message:
    Slp:         The path E:MSSQL10.X\MSSQL\DATA is malformed or not absolute.

    We can see the after E: there is a missing \

    Cause: SQL Server 2008 setup seems to not interpret INSTANCEDIR correctly. It will treat "\"" as an escape character of quotation mark which is wrong.


    Workaround:     Use "E:\\" instead of "E:\" if you want to install SQL to root folder.

    Dedication to Paulo A., he found out another workaround. He removes the double quote like INSTANCEDIR=E:\ instead of INSTANCEDIR="E:\"

    Update : Since the documentation was been updated

    How to: Install SQL Server 2008 R2 from the Command Prompt :
    "Note the use of escaping characters:
    /INSTANCEDIR=c:\PathName is supported.
    /INSTANCEDIR=c:\PathName\ is supported
    /INSTANCEDIR="c:\PathName\\" is supported.
    /INSTANCEDIR="c:\PathName\" is not supported."

    Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |

     

  • Microsoft SQL Server

    Resources disks on a clustered SQL instance

    Posted over 3 years ago
    by

      

    In this post I share with you several recurring questions on managing disks in a cluster failover Windows.


    1. Mount points and SQL Server

    SQL Server supports mount points if and only if at least one letter named as primary disk for instance. Then you can add your mount points.

    SQL Server support for mounted volumes (SQL 2000,2005 and 2008): "SQL Server failover clustered instances fully support mounted drives if the mounted drive is hosted by a cluster drive with a drive letter assigned.Note Because of the number of available drive letters, the number of the virtual instances on a cluster is limited to 25. SQL Server 2005 has the same limitation...."

    Known issue:
    FIX: You are prompted to format the volume when a formatted volume is mounted on a NTFS folder that is located on a computer that is running Windows Server 2008 or Windows Vista


    2. Sharing a disk resource between several group SQL

    A resource disk that is already allocated to group SQL can not be reused for another instance in another SQL group.


    3. Cluster Shared Volumes (CSV) and SQL Server

    CSV is only usable with HyperV for storing virtual machine. It is not so far, an official communication to extend this type of storage to another type of data / configurations.


    In Windows Server® 2008 R2, the Cluster Shared Volumes feature included in failover clustering is only supported for use with the Hyper-V server role


    4.  Using disks dynamic with cluster

    Dynamic disks are not supported natively with Windows 2008 cluster configuration (http://support.microsoft.com/kb/237853).

    However, if you add a third product for managing dynamic disks you can have a support of the editor of the latter.

    On the other hand, you will find the following link for information on basic vs. dynamic disks.
    http://blogs.msdn.com/clustering/archive/2009/12/16/9937435.aspx

    Michel Degremont | Microsoft EMEA
    Product Support Services Developer - SQL Server Core Engineer |

  • Microsoft SQL Server

    Unable to check client components during the installation of SQL Server 2005

    Posted over 3 years ago
    by

      

    Description of the problem: You want to install SQL server 2005 administration tools.
    However during the installation, you do not have the check box to checked for client components. And no error message was raised during the phase of pre-validation installation.


    Cause : In the log, we find the following error message that indicates that Management Tools has been disabled because IE 6 SP1 is not installed on the server:

    Internet Explorer 6 SP1 is not installed, therefore the Business Intelligence Workbench, Report Designer, and SQL Server Workbench features will be disabled.
    Action: Install Internet Explorer 6 SP1 for the Business Intelligence Workbench, Report Designer, and SQL Server Workbench features to be enabled.

    Extract log: %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\ SQLSetup0001_MachineName_WI.log

    For more information : How to: View SQL Server 2005 Setup Log Files

     
    Resolution: Installing Internet Explorer 6 SP1 is a prerequisite: Hardware and Software Requirements for Installing SQL Server 2005
     
    Extract documentation : « Microsoft Internet Explorer 6.0 SP1 or later is required for SQL Server Management Studio, Business Intelligence Development Studio, and the Report Designer component of Reporting Services.»

     

    Michel Degremont | Microsoft EMEA
    Product Support Services Developer - SQL Server Core Engineer |

  • Microsoft SQL Server

    The server principal "sqlLoginName" is not able to access the database "myDatabaseName" under the current security context.

    Posted over 3 years ago
    by

        

    Description of the issue: When you restore a backup database to another server, you may encounter the error message below when you try to connect.

    Msg 916, Level 14, State 1, Line 1
    The server principal "sqlLoginName " is not able to access the database "myDatabaseName" under the current security context.


    Cause: When you restore a backup database to another server, you may experience a problem with orphaned users. That is to say that the SID system view sysuser is not mapped to a SID syslogins existing.

     
    Resolution: To detect orphaned users you can run the following command:
    USE <myDatabaseName>
    sp_change_users_login  @Action='Report';

    You can see that the SID does not match the system views: sys.sysusers and sys.syslogins
    USE <myDatabaseName>
    SELECT sid FROM sys.sysusers WHERE name = 'sqlLoginName'
    SELECT sid FROM sys.syslogins WHERE name = 'sqlLoginName'

    To correct this problem of connection between the server connection account specified by the user and <login_name> the database specified by <database_user>, you can run the following command.
    USE <myDatabaseName>
    EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='sqlLoginName',@LoginName=' sqlLoginName '; 


    For more information:

    Troubleshooting Orphaned Users
    http://msdn.microsoft.com/en-us/library/ms175475.aspx

    PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
    http://support.microsoft.com/kb/274188/

    sp_addlogin (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms173768.aspx

     

     

    Michel Degremont| Microsoft EMEA
    Product Support Services Developer -SQL Server Core Engineer |     

  • Microsoft SQL Server

    How to rebuild System Databases on SQL server 2008 in cluster failover

    Posted over 3 years ago
    by

    When the master, model, msdb, and tempdb system databases are rebuilt, the databases are dropped and re-created in their original location. If a new collation is specified in the rebuild statement, the system databases are created using that collation setting. Any user modifications to these databases are lost.

    Step 1: Review the documentation "before Rebuild the System Databases"

    Rebuild master in SQL Server 2008
    http://msdn.microsoft.com/en-us/library/dd207003.aspx

    Step 2: Put off line SQL Server resource by using Failover Cluster Mamagement

    Step 3: Go on the node where this clustered instance is owned

    Step 4: Then, run the command :

    setup.exe
     /Q
     /ACTION=REBUILDDATABASE
     /SQLSYSADMINACCOUNTS="DomaineName\UserAccount"
     /SQLCOLLATION=CollationName
     /INSTANCENAME="OnlyInstanceName"
    /SAPWD=StrongPassword

    Step 5: Apply the last service pack

    Caution
    -          The INSTANCENAME parameter takes only the instance name, without any server/virtual server name. For the default instance, enter MSSQLSERVER.
    -          If this clustered instance is owned by the current node, the SQL Server resource must be offline or failed.
    -          The specified SA password must meet strong password requirements. For more information about strong password requirements, see "Database Engine Configuration - Account Provisioning" in Setup Help or in SQL Server 2008 Books Online.
    -         The parameter SAPWD is required if the instance uses Mixed Authentication (SQL Server and Windows Authentication) mode.
    -          The collation must be existed (see http://msdn.microsoft.com/en-us/library/ms180175.aspx ).

     

     

    Michel Degremont| Microsoft EMEA
    Product Support Services Developer -SQL Server Core Engineer |

  • Microsoft SQL Server

    SSAS 2000 : Cannot connect to the repository

    Posted over 3 years ago
    by

    When you try to connect to your instance SSAS 2000, you get the following error messages:
    - Cannot connect to the repository
    - Data source name not found and no default driver specified

    In addition connection strings of the repository are empty : open Analysis Manager >> Edit Conection String >> click right on instance name of Analysis Server. Then clic on "Edit Repository Connection String"…


     
    Cause
    Analysis Services 2000 uses CryptAcquireContext to encrypt / decrypt two registers keys : RemoteRepositoryConnectionString and RepositoryConnectionString.

    These keys contain the connection information for access to the database repository used by Analysis Services. This call fails.

    The connection with a UDL file works as it does not need to contact the repository.


    Resolution
    Show hidden files. Then go to C:\Documents and Settings\<userAccount>\Application Data\Microsoft\Crypto\RSA. And rename the directories that conteint. Upon restart of the service you could edit the connection string.

    If the problem persists, create a new domain account and change the service account of SSAS 2000.


    Related Links
    - Default permissions for the MachineKeys folders
    - Specifying Service Accounts
    - Peer-to-Peer Framework APIs return a "PEER_E_NO_KEY_ACCESS" error message


    Michel Degremont    | Microsoft EMEA
    Product Support Services Developer -SQL Server Core Engineer |

  • Microsoft SQL Server

    Configuring Web Synchronization on Windows 2008 64Bit IIS7 for Merge Replication with SQL CE Subscribers

    Posted over 3 years ago
    by
    • 1 Comments


    It this post, you will see how to configure Web Synchronization for Merge Replication over Windows 2008 X64, specifically for SQL Compact Subscribers. I will not cover each and every aspect of Web Sync or Merge Replication for SQL Compact.

    You could find below a merge of various KB and documentation below:

    -KB920700 : An introduction to SQL Server 2005 Compact Edition
    -KB942040 : Error message when you try to visit a Web page that is hosted on a computer that is running IIS 7.0: "HTTP Error 404.2 – Not Found"
    -KB955966 : SQL Server Compact 3.5 Service Pack 1 Server Tools are not installed in WOW mode when you run IIS 7.0 on a 64-bit Windows Server 2008-based computer
    -KB912430: You cannot replicate data from SQL Server 2005 to SQL Server Mobile Edition by using the 64-bit version of IIS

    Prerequisites.
    a. IIS on your server Windows 2008.

    b. One SQL 2005 Instance installed. (You will use this instance to create a sample publication and test it for SQL Compact Subscribers)

    c. User understanding on general Web Sync and Merge Replication process.


    Step 1.Install the Internet Information Services 6.0 (IIS 6.0) backward compatibility components. To do this, use one of the following procedures:

    a.Click Start, click Administrative Tools, and then click Server Manager.

    b.In the left navigation pane, expand Roles, right-click Web Server (IIS), and then click Add Role Services.

    c.In the Select Role Services pane, scroll down to IIS 6 Management Compatibility.

    d.Click to select the following check boxes:
    IIS 6 Metabase Compatibility
    IIS 6 Management Console
    ISAP Extensions
    CGI
    IIS 7 enable backward compatibility components


    e.In the Select Role Services pane, click Next, and then click Install in the Confirm Installations Selections pane.

    f.Click Close to exit the Add Role Services Wizard.


    Step 2.Start Registry Editor (Start >> Run... >> Regedit). And change the MajorVersion setting to 6 under the following registry subkey:
    HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\InetStp

    Step 3.Install :
    - How to: Install SQL Server Replication Components
    - Microsoft SQL Server Compact 3.5 Service Pack 2 Server Tools
    - Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Desktop

     


    Step 4.After the server tools installation has completed successfully, change the MajorVersion setting to 7 under the following registry subkey:
    HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\InetStp

    Step 5.Check ISAPI or CGI restriction

    a.On the taskbar, click Start, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.

    b.In the Connections pane, click the server name.

    c.In the Home pane, double-click ISAPI and CGI Restrictions.

    d.Then check the restriction of sqlcesa35.dll. If not, go in the Actions pane, click Add...

    ISAPI CGI restriction


    Step 6.Check if the application pool are running in 32Bit

    a.On the taskbar, click Start, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.

    b.In the Connections pane, click the server name.

    c.Then click on Applications Pools

    d.Click right on the pool of your website >> Advanced setting

    e.And update Enable for 32 Bit application to TRUE.
    Enable 32 Bit application IIS 7

    Step 7. At this step, it should be work. Go to your website : http://localhost/.../sqlcesa35.dll?. You should get "Microsoft SQL Server Compact Server Agent" or http://localhost/.../sqlcesa35.dll?diag You should get "SQL Server Compact Server Agent Diagnostics"

     

     

    Michel Degremont| Microsoft EMEA
    Product Support Services Developer -SQL Server Core Engineer |

  • Microsoft SQL Server

    SQL Server 2008 R2 Digital Tour

    Posted over 3 years ago
    by
    • 0 Comments



    The SQL Server 2008 R2 Digital Tour has started. Find a host of resources on SQL Server 2008 R2, including videos, whitepapers, e-books, events, test drives and downloads . Also check out these related resources:
     

    - SQL Server 2008 R2 Books Online
    - SQL server 2008 R2 Training kit
    - Download the Microsoft SQL Server 2008 R2 Trial
    - SQL Server 2008 R2 Update for Developers Training Kit

    SQL Server 2008 R2

    Michel Degremont| Microsoft EMEA
    Product Support Services Developer -SQL Server Core Engineer |
  • Microsoft SQL Server

    Managing memory of Analysis Services instance

    Posted over 3 years ago
    by
    • 0 Comments


    Configuring the memory of your instance Analysis Services

    Microsoft Analysis Services (SSAS) has a capacity to improve performance by making data in cache. SSAS can benefit from increased physical memory. You will find below a summary of the limits of memory 32Bit and 64Bit.

    If your operating system and Analysis Service are in 32Bit
    By default in a 32bit environment, the process "Analysis Service" is limited to 2 GB of memory.

    The modification of the / 3GB memory pushes the limits by default. If you want to allocate more than 2Gb to "Analysis Service", you can add the / 3GB switch in the boot.ini to allow "Analysis Service" to use up to 3 GB of memory. Keep in mind that the / 3GB switch reduces the memory available by the Kernel to 1Gb and thus system contention problems can occur.

    CautionIf the machine has more than the 16Gb / 3GB is not supported.
    The 3GB switch will impact all the software on the server that can allocates more than 2Gb. Such as SQL Server Engine

    Example :
    - By default: 2Gb virtual memory for process SSAS and 2Gb for the Kernel.
    - /3GB : 3Gb of virtual memory for process SSAS and 1GB for kernel

    If your operating system is in 64Bit and Analysis Service in 32 Bit
    If your system is operating in 64-bit and Analysis Service in 32 bits, so the process "Analysis Service" is limited to 4 GB of memory.

    If your operating system and Analysis Service are in 64Bit
    For 64-bit machine, you have no limitations, other than that you configured in the properties of the SSAS instance (and of course the available memory on the machine)


    Configuring an instance SSAS
    You can configure the properties for your SSAS instance from the configuration file MSMDSRV.INI. Before you make a configuration change, you should always make a backup of the file:% \ Microsoft SQL Server \ "Instance Name" \ OLAP \ Config \ msmdsrv.ini. All changes are taken into account immediately after you save the file and without restarting the instance.

    By default an instance "Analysis Service" is set to assume almost all of the memory. The default values are appropriate for the majority of servers that have a single instance and OLAP (Analysis Services). From the moment or you  add an additional instance of OLAP or SQL Server in your server, you must change some settings.

    As for SQL Server with the parameter MEMORY MAX(http://msdn.microsoft.com/en-us/library/ms178067.aspx ), We recommend that you change the property values of the memory instance "Analysis Service" on your server multi-instances. All the properties below the value of which is less than 100 are in% while all values greater than or equal to 100 are in Bytes:

    TotalMemoryLimit : 85
    LowMemoryLimit : 75

    Unlike SQL Server, the value TotalMemoryLimit is not an absolute limit because SSAS can  run a query or processing far exceed this value. Take this as an objective value that the engine is fixed.

    Michel Degremont| Microsoft EMEA
    Product Support Services Developer -SQL Server Core Engineer |
  • Microsoft SQL Server

    Migrating a cube in SQL Server Analysis Services 2005 to SQL Server Analysis Services 2008

    Posted over 3 years ago
    by
    • 0 Comments


    Migrating a cube in SQL Server Analysis Services 2005 to SQL Server Analysis Services 2008

    You should consider migrating your cubes with one of 2 methods below:
    1. Method Backup / Restore (recommended)
    2. By copying the DATA directory on the second server while ensuring the SSAS service is stopped on both servers.

    CautionYou can not move your cube (SQL Server Analysis Services 2005) via a sync to your new server (SQL Server Analysis Services 2008). If you try, you get the error message: Errors in the OLE DB provider. The server returned the following error: (The BackupLog element at line 1, column 904 (namespace ) cannot appear under Envelope/Body/Execute/Command/InternalSynch.)Backup and restore errors: An error occurred while synchronizing the MyDB' database, Database synchronization failed. . The synchronization works only with the same versions of SQL server and the same service pack level.


    Example with the method Backup/Restore:

    Step 1 : Backup of the database on the instance SSAS 2005

    Method using the Wizard
    - In Management Studio, connect to your instance SSAS 2005
    - Click right on database backups
    - Then click on « Back up… »
    - Then select the location of the backup file and click OK

    Method using a script XMLA
    - In Management Studio, connect to your instance SSAS 2005
    - Click on "File" >> "New" >> "Analysis Services XMLA Query"
    - Then execute the code below :

    <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <    Object>
    <    DatabaseID>MyDB</DatabaseID>
    </    Object>
    <    File>C:\MyDB.abf</File>
    </    Backup>


    Step 2 :     Move the file. Abf to the server that hosts your new instance SSAS 2008

    Step 3 : The last step is to restore the backup file in step 1.

    Method using the Wizard
    - In Management Studio, connect to your instance SSAS 2008
    - Click right on «Databases »
    - Then click on « Restore… »
    - Then select the location of the file to restore and click OK

    Method using a script XMLA
    - In Management Studio, connect to your instance SSAS 2008
    - Click right on "File" >> "New" >> "Analysis Services XMLA Query"
    - Then execute the code below :

    <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <    File>C:\MyDB.abf</File>
    <    AllowOverwrite>true</AllowOverwrite>
    </    Restore>

    For more information: Managing Backing Up and Restoring (Analysis Services 2008)

    Michel Degremont| Microsoft EMEA
    Product Support Services Developer -SQL Server Core Engineer |
  • Microsoft SQL Server

    How to use the Sqldumper.exe utility to generate a dump file in SQL Server 2008 in T-SQL

    Posted over 3 years ago
    by
    • 0 Comments


    How to use the Sqldumper.exe utility to generate a dump file in SQL Server 2008 in T-SQL

    If you need to generate a full memory dump, automatically when a specific SQL Alert occurs, you can use the script T-SQL below


    declare @pid int
    declare     @str varchar(1024)
    declare @dumpdir varchar(255)
    declare @sqlbindir varchar(255)

    set @dumpdir = 'C:\'
    set @sqlbindir = 'C:\Program Files\Microsoft SQL Server\100\Shared\'
    set @pid=convert(int, SERVERPROPERTY('ProcessID'))
    set @str = 'cd ' + @sqlbindir + '&& "sqldumper.exe" '+convert(varchar(10), @pid)+' 0 0x0800 0 ' + @dumpdir

    exec xp_cmdshell @str

     More detail about SQLDumper option below :


    Usage: SqlDumper.exe [ProcessID
    [ThreadId [Flags[:MiniDumpFlags] [SqlInfoPtr [DumpDir [ExceptionRecordPtr [ContextPtr [ExtraFile]]]]]]]]
    [-I<InstanceName>]
    [-S<ServiceName>]
    [-remoteservers:[print|dump|freeze|resume|remote:guid\dumporigin\signature\localId\port\operationType]]

      Flags:
        dbgbreak            = 0x0001
        nominidump          = 0x0002
        validate_image      = 0x0004
        referenced_memory   = 0x0008
        all_memory          = 0x0010
        dump_all_threads    = 0x0020
        match_file_name     = 0x0040
        no_longer_used_flag = 0x0080
        verbose             = 0x0100
        wait_at_exit        = 0x0200
        send_to_watson      = 0x0400
        defaultflags        = 0x0800
        maximumdump         = 0x1000
        mini_and_maxdump    = 0x2000
        force_send_to_watson= 0x4000
        full_filtered_dump  = 0x8000

      MiniDumpFlags:
        Normal                           = 0x0000
        WithDataSegs                     = 0x0001
        WithFullMemory                   = 0x0002
        WithHandleData                   = 0x0004
        FilterMemory                     = 0x0008
        ScanMemory                       = 0x0010
        WithUnloadedModules              = 0x0020
        WithIndirectlyReferencedMemory   = 0x0040
        FilterModulePaths                = 0x0080
        WithProcessThreadData            = 0x0100
        WithPrivateReadWriteMemory       = 0x0200
        WithoutOptionalData              = 0x0400
        WithFullMemoryInfo               = 0x0800
        WithThreadInfo                   = 0x1000

    Michel Degremont| Microsoft EMEA
    Product Support Services Developer -SQL Server Core Engineer |
  • Microsoft SQL Server

    Best practice regarding the dependency to the SQL Server resource for the additional disk

    Posted over 3 years ago
    by
    • 0 Comments


    Best practice regarding the dependency to the SQL Server resource for the additional disk.

    If a non-SQL Server resource needs to be added to the SQL Server group and depends on the SQL Server resource being available, Microsoft recommends that you add a dependency on the SQL Server Agent resource instead of adding a dependency on the SQL Server resource.

    In the case where, by mistake, you change the dependency of one disk of SQL Server, you will get the error message below.

    Error 5184: Cannot use file '%.*ls' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sq...

    So take care when you play with the dependency to the SQL Server resource.


    See also :
    - How to: Add Dependencies to a SQL Server Resource
    - How to create databases or change disk file locations on a shared cluster drive on which SQL Server was not originally installed


    Michel Degremont| Microsoft EMEA
    Product Support Services Developer -SQL Server Core Engineer |
  • Microsoft SQL Server

    How to change the dump directory for SQL Server or SQL Agent

    Posted over 3 years ago
    by
    • 0 Comments


    How to change the dump directory for SQL Server or SQL Agent

     

    SQL Server creates dump files (files with a .mdmp extension) by default under the LOG folder. If you don't know where is this folder, you can run the query below :

    CREATE TABLE #temp (logDate varchar(50), processInfo varchar(50), [Text] text);
    INSERT #temp EXEC ('sp_readerrorlog');
    SELECT [Text] FROM #temp WHERE [Text] like '%Logging SQL Server messages in file%';
    DROP TABLE #temp;

    These dump files are generally created when SQL Server encounters an exception or access violation.


    If you see these kind ofdump files :
    - First, check if you are the last service pack for your SQL server version.
    - If the issue are still facing to this issue,it might be good idea to contact SQL Server Support team to ensure everything is fine with your server.

     

    If your LOG folder is located on a disk drive that doesnt have enough space left on it, you can change that value to a location on a drive that has enough space. So, since SQL 2005, you can change the default dump directory inside Configuration Manager. Don't forget that it's not supported to change the value directly into the Registry.

     

    Step 1: Start the Configuration Manager from Configuration Tools menu.

    Step 2: Double click on SQL Server (instancename) service

    Step 3: And go to ADVANCED tab. You will see the option for DUMP DIRECTORY.

    How to change the dump directory for SQL Server or SQL Agent

    The changes will be applied only after a restart.


    Michel Degremont| Microsoft EMEA
    Product Support Services Developer -SQL Server Core Engineer |
  • Microsoft SQL Server

    Setup SQL Server 2008 : Service SQLBrowser start request failed

    Posted over 3 years ago
    by
    • 0 Comments


    This week I was working on SQL server 2008 setup problem in cluster. But this issue could be occurs also in a standalone installation.


    Action: Run SQL Setup  on the second node. The SQL Server 2008 setup was working fine at the beginning but failing almost at the end of the process with the following error message: SQLBrowser start request failed. Click Retry to retry the failed action, or click Cancel to cancel this action and continue setup


    Result: Unable to add a second node on an existing cluster SQL Server  2008

    SQLBrowser: The last attempted operation: Starting the SQL Server Browser service 'SQLBrowser', and waiting for up to '900' seconds for the process to   complete. .
    Slp: Prompting user if they want to retry this action due to the following failure:
    Slp: ----------------------------------------
    Slp: The following is an exception stack listing the exceptions in outermost to innermost order
    Slp: Inner exceptions are being indented
    Slp:
    Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
    Slp:     Message:
    Slp:         Service 'SQLBrowser' start request failed.
    Slp:     Data:
    Slp:       Feature = SQL_Browser_Redist_SqlBrowser_Cpu32
    Slp:       Timing = Startup
    Slp:       DisableRetry = true 


    Resolution:     I solved this issue by slipstream the setup including SP1 directly. I invite you to follow the article KB below:

    How to update or slipstream an installation of SQL Server 2008
    http://support.microsoft.com/kb/955392

    For answers to frequently asked questions about slipstreaming, see the SQL Server 2008 Slipstream Frequently Asked Questions topic on the following Microsoft Web site:
    http://blogs.msdn.com/petersad/archive/2009/04/24/sql-server-2008-slipstream-frequently-asked-questions.aspx

    Step 1: Download the service pack package that matches you system architecture. For example download the x64 package of SQL Server 2008 Service Pack 1 if your system is an x64-based system.


    Step 2:     Extract the service pack by running the following command:
    SQLServer2008SP1-KB968369-x64-ENU.exe /x:C:\SQL2008SP1
    slipstream an installation of SQL Server 2008

    Step 3:     Run the following file to install the setup support files:
    C:\SQL2008SP1\x64\setup\1033\sqlsupport.msi


    Step 4: Run the Setup.exe file from the SQL Server 2008 source media by specifying the /PCUSource parameter. For example:
    Setup.exe /PCUSource=C:\SQL2008SP1
    slipstream an installation of SQL Server 2008

    And finish your installation


    Michel Degremont | Microsoft EMEA
    Product Support Services Developer - SQL Server Core Engineer |
  • Microsoft SQL Server

    Last stored procedures updated with SQL Server Management Studio 2008

    Posted over 3 years ago
    by
    • 0 Comments


    Last stored procedures updated with SQL Server Management Studio 2008

    I would like to share with you a new feature that I found out by using SQL Server Management Studio 2008.

    With SQL Server 2005, I used to run the query below to get the list of the stored procedure whom modified by the developper recently.

    SELECT TOP(10)
                     [name]    , [object_id], [create_date], [modify_date]
    FROM
                         sys.all_objects
    WHERE
                    [type]     = 'P' AND [object_id] > 0
    ORDER BY
                     [modify_date]     DESC


    System view : sys.all_objects
    http://msdn.microsoft.com/en-us/library/ms178618.aspx

     


    Now, with SQL Server Management Studio 2008, we can customize the Object Explorer Details window by adding and removing the columns you wish to see. To add and remove columns from the Object Explorer Details window, right-click an existing column and check or uncheck columns from the context menu as shown in the following image.

    Step 1: Open SQL Server Management Studio 2008

    Step 2: Click on view >> Object explorer details

    Step 3: In Object Explorer, connect to an instance of Database Engine and then expand that instance

    Step 4: Expand Databases, expand your database, then expand Programmability. And click on Stored procedures


     SQL Server Management Studio last stored procedure updated

    Michel Degremont | Microsoft EMEA
    Product Support Services Developer - SQL Server Core Engineer |
  • Microsoft SQL Server

    SQL Server 2008 R2 Update for Developers Training Kit

    Posted over 3 years ago
    by
    • 0 Comments


    SQL Server 2008 R2 Update for Developers Training Kit
    SQL Server 2008 R2 developer training kit will help you to take advantage of the improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in your application.

    Download :

    - SQL Server 2008 R2 Update for Developers Training Kit
    - SQL Server 2008 Developer Training Kit



    SQL Server 2008 R2

     

    Michel Degremont | Microsoft EMEA
    Product Support Services Developer - SQL Server Core Engineer |

Page 2 of 3 (75 items) 123