[좐]군의 To-Be

(0) (0)

Posted by 좐군

[Install a certificate on a server with Microsoft Management Console (MMC)]

1. To open the Certificates snap-in, follow these steps:
   1. To open the MMC console, click Start, and then click Run. In the Run dialog box type:
      
      MMC
   2. On the Console menu, click Add/Remove Snap-in....
   3. Click Add, and then click Certificates. Click Add again.
   4. You are prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.
   5. Select Local computer, and then click Finish.
   6. Click Close in the Add Standalone Snap-in dialog box.
   7. Click OK in the Add/Remove Snap-in dialog box. Your installed certificates are located in the Certificates folder in the Personal container.
2. Use the MMC snap-in to install the certificate on the server:
   1. Click to select the Personal folder in the left-hand pane.
   2. Right-click in the right-hand pane, point to All Tasks, and then click Request New Certificate....
   3. The Certificate Request Wizard dialog box opens. Click Next. Select Certificate type is "computer".
   4. In the Friendly Name text box you can type a friendly name for the certificate or leave the text box blank, and then complete the wizard. After the wizard finishes, you will see the certificate in the folder with the fully qualified computer domain name.
      
      
   5. If you want to enable encryption for a specific client or clients, skip this step and proceed to the Enable encryption for a specific client section of this article.
      
      If you want to enable encryption at the server, open the Server Network Utility on the server where the certificate is installed, and then click to select the Force protocol encryption check box. Restart the MSSQLServer (SQL Server) service for the encryption to take effect. Your server is now ready to use SSL encryption.

[Enable encryption for a specific client]

1. To export the server certificate's Trusted Root Certificate Authority (CA), follow these steps:
   1. Open MMC, and then locate your certificate in the Personal folder.
   2. Right-click the certificate name, and then click Open.
   3. Review the Certification Path tab. Note the top most item.
   4. Navigate to the Trusted Root Certification Authorities folder, and then locate the Certificate Authority noted in step c..
   5. Right-click CA, point to All Tasks, and then click Export.
   6. Select all the defaults, and then save the exported file to your disk where the client computer can access the file.
2. Follow these steps to import the certificate on the client computer:
   1. Navigate to the client computer by using the MMC snap-in, and then browse to the Trusted Root Certification Authorities folder.
   2. Right-click the Trusted Root Certification Authorities folder, point to All Tasks, and then click Import.
   3. Browse, and then select the certificate (.cer file) that you generated in step 1. Select the defaults to complete the remaining part of the wizard.
   4. Use the SQL Server Client Network Utility.
   5. Click to select the Force Protocol encryption option. Your client is now ready to use SSL encryption.

[How to test your client connection]

• Use the Query Analyzer Tool.
  
  -or-
  
  
• Use any ODBC application where you can change the connection string.

1. Use the SQL Server Client Network Utility.
2. Click to select the Force protocol encryption option.
3. Connect to the server that is running SQL Server 2000 by using Query Analyzer.
4. Monitor the communication by using Microsoft Network Monitor or a Network Sniffer.

1. Modify the ODBC or OLEDB connection string. For example:
   
   ODBC

   Driver=SQLServer;Server=ServerNameHere;UID=UserIdHere;PWD=PasswordHere;Network=DBNETLIB.DLL;Encrypt=YES

   OLEDB

   Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbNameHere;Data Source=ServerNameHere;Use Encryption for Data=True

2. Connect to the server that is running SQL Server 2000, and then monitor the communication by using Microsoft Network Monitor or a Network Sniffer.

[Troubleshooting]

1. Open the Certificates snap-in. To do this, see step 1 in the "How to Configure the MMC Snap-in" section.
2. In the Certificates snap-in, expand Personal, and then expand Certificates.
3. In the right pane, locate the certificate that you installed.
4. Determine whether the certificate meets the following requirements:
   • In the right pane, the value in the Intended Purpose column for this certificate must be Server Authentication.
   • In the right pane, the value in the Issued To column must be the server name.
5. Double-click the certificate, and then determine whether the certificate meets the following requirements:
   • On the General tab, you receive the following message:
     You have a private key that corresponds to this certificate.
   • On the Details tab, the value for the Subject field must be server name.
   • The value for the Enhanced Key Usage field must be Server Authentication (<number>).
   • On the Certification Path tab, the server name must appear under Certification path.

(0) (0)

Posted by 좐군

One of the more visible changes we made in connectivity space for SQL Server 2005 was enhancing error messages reported to the user in case of connection failures.  I have seen several cases over the last several months, and in this post I would like to list some of them with the root cause that caused them.  The cause may not be the only one triggering a particular error message, and there may be other error messages of interest. 

This is a semi-random selection from real cases I investigated.  Likely I will post another batch some time in the future, and I would be happy to see replies with additional errors, particularly if the root cause is unclear. 

The examples are from various client stacks – ODBC or OLEDB from SQL Native Client or from managed SqlClient. 

The exact formatting of the messages will depend on the application you use.  Most of the examples below used OSQL, SQLCMD, or SQL Server Management Studio. 

• Connecting to a server by the server’s name from SqlClient, the server name is aliased to TCP, the server is up and running but it does not listen on TCP (or is not running at all):

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

• Local connection from SqlClient; server is not running:

(a) default instance:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, 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: 2)

(b) named instance:

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

• Remote connection from ODBC, Windows Firewall is turned on on the server machine but there is an exception for File and Printer Sharing:

 [SQL Native Client]Unable to complete login process due to delay in opening server connection

• A successful TCP connection from SqlClient to the server was broken due to a TCP keep-alive heartbeat failure (typically indicating problems with the underlying network infrastructure):

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Possibly:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

• OBDC connection attempt when server is not ready to process a new local connection, possibly due to overload: 

[SQL Native Client]Shared Memory Provider: Timeout error [258].
[SQL Native Client]Login timeout expired
[SQL Native Client]Unable to complete login process due to delay in prelogin response

[SQL Native Client]Shared Memory Provider: Could not open a connection to SQL Server [121].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

• There is a space after server name in the connection string:

osql -E -S "<serverName> ,1433"
Login failed for user ''. The user is not associated with a trusted SQL Server connection.

The server ERRORLOG/EventLog shows something similar to:

2005-08-11 12:46:04.29 Logon       Error: 17806, Severity: 20, State: 2.
2005-08-11 12:46:04.29 Logon       SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: <IP address>]
2005-08-11 12:46:04.29 Logon       Error: 18452, Severity: 14, State: 1.
2005-08-11 12:46:04.29 Logon       Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: <IP address>]

• Remote OLEDB connection using TCP to a server that is blocked by Firewall

HResult 0x274C, Level 16, State 1
TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Error: Microsoft SQL Native Client : Login timeout expired.

• Remote OLEDB connection using TCP to a server that is either not running or does not have TCP/IP protocol enabled for incoming connections (but is not blocked by the Firewall on the server machine):

HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively refused it.

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Error: Microsoft SQL Native Client : Login timeout expired.

출처 : http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx

(0) (0)

Posted by 좐군

With windows connection color indicator you always know to which server you're connected to. The color strip can be docked to any side of the window and it's color can be easily changed from either it's context menu or from server coloring options. Coloring for each server can be added, removed or just disabled.

Query Execution History (Soft Source Control) and Current Window History

Every SQL statement that you run is logged in a file on your disk or in a table in a database you specify with a connection string. This way, if you're working on some script you can get the full history of every change you've made between check-out and check-in of your file. SQL Statements are saved in a list that is written to a file and/or a database with a timer which you can set to a desired interval. Older query history logs can be auto deleted. Time after the logs are deleted is user settable.

Current window history is a dockable window that show queries executed in a currently active window. There is also a search box at the top that filters results as you type.

Search Table or Database Data

Simple search for a search term through all non-binary columns in a single table or in every table of a database. You can search in four ways: Contains, Starts with, Ends with and Equals.

Uppercase/Lowercase keywords and proper case Database Object Names

Set all keywords to uppercase or lowercase letters. Custom keywords can be added. Format all database objects to their proper case sensitive name.

Run one script on multiple databases

Run selected or full window text on selected databases on the currently connected server. A new window is opened that contains one script for all databases.

Copy execution plan bitmaps to clipboard

Copy selected or all execution plans to a bitmap that is saved on the clipboard. Large execution plans that don't fit in the window are also copied in full. Width of a picture containing all execution plans is equal to the width of the widest execution plan.

Search Results in Grid Mode and Execution Plans

Find all occurrences of your search string in the execution plans or in the results in datagrid mode.

Generate Insert statements for a single table, the whole database or current resultsets in grids

Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and 10 Mb. Larger values then the limit are then scripted as NULL. Insert statements for the data in result grids are scripted into a new temporary table for each grid. For example from 5 result grids insert statements for 5 temporary tables get created.

Text document Regions and Debug sections

Regions behave in the same way as in Visual Studio. You can collapse them and expand them. Debug sections are sections that get commented or deleted when you change your script to Release configuration. A debug section is also a collapsable region. If you deploy a script in debug mode with added debug sections it will fail when run from SSMS without SSMS Tools Pack installed. You can of course comment those sections yourself by simply searching for start and end text of the debug sections.

Running custom scripts from Object explorer's Context menu

You can specify a custom script text, its name and node on Object Explorer on which you want to run it from. Node name, current database, current connection string and current server can also be specified in the custom script with keywords that are replaced with their respective values at script runtime.

CRUD (Create, Read, Update, Delete) stored procedure generation

CRUD stored procedure generation for tables based on fully customizable templates that you can change to suit your needs.

New query template

When opening a new query window you can specify a template that will be displayed.

(0) (0)

Posted by 좐군

(0) (0)

Posted by 좐군

(0) (0)

Posted by 좐군

[SQL Server]SQL Server 2005 XML ASCII 문자(1~31) 사용시 오류 문제.

1. 오류 재현 코드

실행결과

위 실행결과에서 확인할 수 있듯이 ASCII코드 1에서 31사이에 사용 가능한 Character는

9  (TAB : horizontal tab, XML #x9)

10 (LF : line feed, new line, XML #xA)

13 (CR : carriage  return, XML #xD)

이렇게 세가지 뿐이고, 나머지 Character는 다음과 모두 에러를 발생한다.

PRB: Error Message When an XML Document Contains Low-Order ASCII Characters

원문 : http://support.microsoft.com/kb/315580

SYMPTOMS (증상)

CAUSE

REFERENCES

(0) (0)

Posted by 좐군

(0) (0)

Posted by 좐군

(0) (0)

Posted by 좐군

(0) (0)

Posted by 좐군