The Key's Personal store: MSSQL

레이블이 MSSQL인 게시물을 표시합니다. 모든 게시물 표시

2016년 10월 18일 화요일

MSSQL 에서 오라클로 LINKED SERVER 생성시 오류

MSSQL 에서 오라클로 LINKED SERVER 생성시 오류 발생

연결된 서버 "SERVER_NAME"에 대한 OLE DB 공급자 "OraOLEDB.Oracle"의 인스턴스를 만들 수 없습니다. (Microsoft SQL Server, 오류: 7302) 도움말을 보려면 다음을 클릭하십시오:

이 사이트에서 해결책 확인

오라클 클라이언트 설치 후 SQLSERVER 재시작 해야됨.;;

2016년 5월 10일 화요일

bulk insert 시 에러.

아래와 같은 에러 메시지 발생

메시지 4866, 수준 16, 상태 8, 줄 1
대량 로드하지 못했습니다. 데이터 파일의 행 1, 열 24에서 열이 너무 깁니다. 필드 종결자와 행 종결자를 제대로 지정했는지 확인하십시오.
메시지 7301, 수준 16, 상태 2, 줄 1
연결된 서버 "(null)"의 OLE DB 공급자 "BULK"에서 필수 인터페이스("IID_IColumnsInfo")를 가져올 수 없습니다.

필드 종결자를 바꿈.
\r\n 이 아님.
\n 으로 바꾸고 잘됨.

관리하는 사이트 중에 우편번호를 DB 에 저장해서 사용하는 곳이 있다.
DB에 저장해서 사용하다 보니 주기적으로 계속 업데이트를 해 줘야 한다.
sql server management studio 프로그램의 데이터 가져오기를 이용해서 넣어 줬다.

우체국 사이트에서 제공하는 데이터가 각 도별 txt 파일이다.
우리나라의 전체 도가 17개인데, ssms 프로그램으로 넣으려면 무려 반복 노가다를 17번을 해야 한다.

bulk insert를 이용해서 한번에 insert 시키도록 하였다.
bulk insert를 하려면 양식(formatfile)이 필요하다.
cmd 창을 열고 아래와 같이 입력해서 뽑아 내자.

C:\Users\Administrator>bcp mydb.dbo.T_ZIPCODE format nul -c -x -f crmZipcode.a.xml -t"|" -T

해당 파일을 바로 사용 하면 좋은데, 우체국 제공 컬럼과 실제 컬럼이 일치 하지 않는 다. 수정이 필요하다.
관리 사이트와 우체국 우편번호 데이터 컬럼의 다른 점은 다음과 같다.
1. 20번 컬럼인 '행정동' 컬럼이 없다.
2. 25번 컬럼인 '구우편번호' 컬럼이 없다.
3. 26번 컬럼인 '우편번호일련번호' 컬럼이 1번째에 있다.

변경하면 다음과 같다.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
 <!--파일의 필드 -->
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="14" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="60" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="60" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="60" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="100" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="100" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="160" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="500" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="400" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="16" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="500" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="17" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="60" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="18" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="19" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="20" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="100" COLLATION="Korean_Wansung_CI_AS"/> <!-- 행정동 -->
  <FIELD ID="21" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="100" COLLATION="Korean_Wansung_CI_AS"/> <!--산여부 -->
  <FIELD ID="22" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="23" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="24" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="25" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="26" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="12"/>
 </RECORD>
 <!--서버의 필드 -->
 <ROW>
  <COLUMN SOURCE="26" NAME="SEQ" xsi:type="SQLINT"/>
  <COLUMN SOURCE="1" NAME="ZIPCODE" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="SIDO" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="ESIDO" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="SIGUNGU" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="ESIGUNGU" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6" NAME="EUPMRN" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="7" NAME="EEUPMRN" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="ROADCODE" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9"  NAME="ROADNAME" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="10" NAME="EROADNAME" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="11" NAME="ZIYN" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="12" NAME="GUNBON" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="13" NAME="GUNBU" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="14" NAME="GUNSEQ" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="15" NAME="DABE" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="16" NAME="GUNNAME" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="17" NAME="BUBCODE" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="18" NAME="BUBDONG" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="19" NAME="RI" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="21" NAME="SANYN" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="22" NAME="ZIBON" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="23" NAME="EUPSEQ" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="24" NAME="ZIBU" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>
<!-- 서버의 필드에 없는 것은 컬럼을 넣지 않음. -->
<!-- 파일의 필드는 쓰던 쓰지 않던 모두 넣음 -->
<!-- 베이스가 되는 파일 생성 명령어 : C:\Users\Administrator>bcp mydb.dbo.T_ZIPCODE format nul -c -x -f crmZipcode.a.xml -t"|" -T -->

이 양식을 이해 하는 데, 조금 오래 걸렸다.
간단히 설명하면,
1. 상단 RECORD 는 파일의 컬럼이다. 전부 있어야 된다.
2. 하단 ROW는 테이블의 컬럼이다. 수정해서 써야 한다.
내껄 보면 26번이 처음 있고, 20번과 25번이 없는 걸 알 수 있다.
bulk insert 쿼리는 다음과 같다.

BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\강원도.txt'            WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\경기도.txt'            WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\경상남도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\경상북도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\광주광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\대구광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\대전광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\부산광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\서울특별시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\세종특별자치시.txt'    WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\울산광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\인천광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\전라남도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\전라북도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\제주특별자치도.txt'    WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\충청남도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\충청북도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )

FROM 절과 formatfile 의 위치를 수정해서 사용하면 된다.
path를 변수로 만들고 싶었으나, 기본적으로 지원하지 않고, 동적 sql을 사용해야 한다길래 그냥 뒀다.

대략 30분가량의 노가다를 없앴다. :)

2016년 5월 4일 수요일

MSSQL bak 파일을 이용하여 복원

백업된 bak 파일을 복원
1. 복구할 DB 선택, 우클릭 속성 , 옵션 -> 엑세스 제한을 RESTRICTED_USER 로 변경.

(
RESTRICTED_USER
RESTRICTED_USER를 사용하면 db_owner 고정 데이터베이스 역할과 dbcreator 및 sysadmin 고정 서버 역할의 멤버만 데이터베이스에 연결할 수 있습니다. 연결할 수 있는 멤버의 수에는 제한이 없습니다.데이터베이스에 대한 모든 연결은 ALTER DATABASE 문의 termination 절에 지정된 시간대에 끊어집니다.데이터베이스가 RESTRICTED_USER 상태로 바뀐 후 자격이 없는 사용자의 연결 시도는 거부됩니다.
)

2. 복구할 DB 선택, 태스크 -> 복원 -> 파일 및 파일 그룹 선택
3. 복원에 사용할 원본을 장치 -> bak 파일 선택
5. 옵션에서 기존 데이터베이스 덮어쓰기 체크
6. 실행.
검색하다 보면 블로그에 mssql DB 복원 관련 글에서 엑세스 제한을 single user로 변경하고 복원하라는 글이 있는데,
이렇게 하면 기존 웹프로그램이나, 기타 DB 접속 프로그램이 먼저 선점하면 아주 골치 아픈 일이 발생한다.

2014년 9월 12일 금요일

모든 쿼리 로그 기록.

마리아 db, mysql 의 모든 쿼리를 쌓아놓고 분석에 이용.

my.cnf 에 추가

#log
general_log=1
#log_output을 FILE 로 할경우의 경로.
#general_log_file = /var/log/mysql/general_query.log
log_output=TABLE

아래의 스크립트를 하루에 한번 실행하다록 크론에 등록.

general log가 계속 쌓이는 것을 방지. 7일마다 삭제.

#!/bin/sh

id="root"
pass="PASS"

mysql -u ${id}  -p${pass} -e "SET GLOBAL general_log = 'OFF'";
mysql -u ${id}  -p${pass} -e "RENAME TABLE mysql.general_log TO mysql.general_log2;";
mysql -u ${id}  -p${pass} -e "DELETE FROM mysql.general_log2 WHERE event_time <= NOW()-INTERVAL 7 DAY";
mysql -u ${id}  -p${pass} -e "OPTIMIZE TABLE general_log2";
mysql -u ${id}  -p${pass} -e "RENAME TABLE mysql.general_log2 TO mysql.general_log";
mysql -u ${id}  -p${pass} -e "SET GLOBAL general_log = 'ON'";

http://stackoverflow.com/questions/9343001/is-my-mysql-general-log-table-getting-too-big

general_log 의 엔진이 CSV라서 해당 파일 자체를 cat, vim 등으로 그냥 볼 수 있음.

2014년 8월 19일 화요일

pid 값으로 강제 종료 스크립트.

service mysqld stop으로 mysql이 죽지를 않는다.

pkill mysqld 도 안 먹는다.

killall mysqld 도 안 먹는 다.

kill -9 pid 로는 먹는다.

근데 전부 다 죽여줘야 한다.

스크립트를 찾았다. 역시 있었다.

#!/bin/bash

[ -z "$1" ] && echo "Usage : Input Process .......... Please [scriptFileName ProcessName]" && exit

process_id=`ps -ax | grep "$1" | grep -vw "grep" | grep -vw $$ | awk '{print $1}'`

if [ -z "$process_id" ];then
echo "+-------------------------------------------------------------+"
echo "Not Found Process (입력하신 프로세스를 찾지 못했습니다.) ...... Done"
echo "+-------------------------------------------------------------+"
exit
else
process_id_number=`ps -ax | grep "$1" | grep -vw "grep" | grep -vw $$ | awk '{print $1}'`

for i in ${process_id_number} ;do
kill -9 $i &> /dev/null
printf "%-40s %-s\n" "$i PID Killed" "$(echo -ne "[ \\033[01;32m OK \\033[0m ]")"
done
sleep 2;
echo "+-------------------------------------------------------------+"
echo "Process Kill OK (프로세스가 강제적으로 종료 되었습니다.) ...... Done"
echo "+-------------------------------------------------------------+"
fi

출처

2014년 2월 14일 금요일

mssql sa와 administrator 윈도우 인증 모두 막힌 경우 패스워드 변경

실수로 sa 계정을 막고, administrator 계정을 삭제해 버렸다.

이 말은 관리자 권한으로 mssql에 접속 할 수 없다는 말.

검색 봤더니 죄다 윈도우 인증 계정으로 들어가서 바꾸라는 말 밖에 없다.

여기서 내 문제와 같은 경우를 찾을 수 있었다.

mssql 2012를 사용중인데, 해당 내용으로 들어가면 시작매개변수를 변경 할 수 없게끔 되어있다.

cmd 창을 열고 명령어로 단일 사용자 모드로 실행 하였다.

net stop mssqlserver
#net start mssqlserver /m
net start mssqlserver /f

실행 옵션을 /m 으로 주고 실행하면

로그온트리거에 기록이 남아서 필터링이 되지만 '서버가 단일 사용자 모드에 있으므로 지금은 한 관리자만 연결할 수...' 이런 에러를 뱉어 내지만,

/f 를 주면 로그온트리거에도 걸리지 않는다고 한다.

/f -- 강제모드 실행. 최소 구성으로 실행. 단일 사용자 모드

매우 유용한 정보~!! 감사~

2014년 1월 28일 화요일

원격데스크탑,MSSQL 공격 자동 차단 스크립트

원격데스크탑 과 mssql 의 기본 포트를 바꾸지 않는 이상, BRUTE FORCE 공격이 지속적으로 들어온다.

해당 포트를 바꾸는 게 가장 최선이지만, 사정상 바꾸기 힘든 경우는 이벤트로그를 보고 해당 IP를 차단하는 방법 밖에 없다.

리눅스 같은 경우 fail2ban 이라는 훌륭한 오픈소스가 존재하여 사용가능 하나 윈도우 같은 경우 그런 프로그램이 없다.

소스포지에서 검색 해보면 비슷한 프로그램들이 나오나, 동작도 제대로 안 되고 에러 투성이 프로그램들만 있어서 사용할 수 있는 게 없다.

물론 상용프로램은 제외.

RDP 공격 같은 경우 EvlWatcher 라는 프로그램이 있는데, 아주 잘 동작 된다.

주의 할점은 관리자 권한으로 설치 해야 서비스 등록 및 방화벽 룰 셋팅이 제대로 설정 된다.

그러나 MSSQL BRUTE FORCE 공격은 따로 막아 주지 않아 조금 아쉽다.

ps.2014.05.21 윈도우2012에서는 동작을 안한다.

-------------------------------------------------------------------------------------

그러다 파워셀 스크립트로 해당 기능을 수행 할 수 있지 않을 까 해서 검색 해 보니, 역시 있었다.

해당 스크립트를 약간 변형 하여 mssql 도 막을 수 있게끔 처리 하였다.

이 스크립트는 이벤트 로그 기록을 참조하여 현재 접속 중인 아이피만 확인 한다.

#$regex1,$regex2값을 서버 아이피로 변경한다.
#예를 들어 서버 아이피가 222.222.222.222 이면
# $regex1 = [regex] "222\.222\.222\.(?:222|51):3389\s+(\d+\.\d+\.\d+\.\d+)";
#제일 마지막은 or 연산이기 때문에 신경 안써도 된다. 한개만 들어가도 됨.

###################### Config ###################### 
 $regex1 = [regex] "111\.222\.333\.(?:140|51):3389\s+(\d+\.\d+\.\d+\.\d+)";
 $regex2 = [regex] "원본 네트워크 주소:\t(\d+\.\d+\.\d+\.\d+)";

 $regex1_mssql = [regex] "111\.222\.333\.(?:140|51):1433\s+(\d+\.\d+\.\d+\.\d+)";
 $regex2_mssql = [regex] "클라이언트: (\d+\.\d+\.\d+\.\d+)";

 $MyIp = "123.123.123.123"; #현재 내가 접속한 IP 차단하지 않는다.
 $deny_count = 5;  #임계값
 $loop_time = 30;  #loop_time 마다 재 실행.(초)
###################### Config ###################### 

  $tick = 0; 
 "Start to run at: " + (get-date); 

 while($True) { 
  $blacklist = @(); 

 #Port 3389 RDP
 "Running... (tick:" + $tick + ")"; $tick+=1; 

 $a = @() 
 netstat -no | Select-String ":3389" | ? { $m = $regex1.Match($_); 
   $ip = $m.Groups[1].Value; if ($m.Success -and $ip -ne $MyIp) {$a = $a + $ip;} } 

 if ($a.count -gt 0) { 
   $ips = get-eventlog Security -Newest 1000 | Where-Object {$_.EventID -eq 4625 } | foreach { 
     $m = $regex2.Match($_.Message); $ip = $m.Groups[1].Value; $ip; } | Sort-Object | Tee-Object -Variable list | Get-Unique 

   foreach ($ip in $a) { if ($ips -contains $ip) { 
     if (-not ($blacklist -contains $ip)) { 
       $attack_count = ($list | Select-String $ip -SimpleMatch | Measure-Object).count; 
       "Found RDP attacking IP on 3389: " + $ip + ", with count: " + $attack_count; 
       if ($attack_count -ge $deny_count) {$blacklist = $blacklist + $ip;} 
     }  
    } 
   } 
 } 

 #Port 1433 MSSQL

 $a = @() 
 netstat -no | Select-String ":1433" | ? { $m = $regex1_mssql.Match($_); 
   $ip = $m.Groups[1].Value;
   if ($m.Success -and $ip -ne $MyIp) {$a = $a + $ip;} } 

 if ($a.count -gt 0) { 
   $ips = get-eventlog Application -Newest 1000 | Where-Object {$_.EventID -eq 18456 -and ($_.Message -like "*sa*" )  } | foreach { 
     $m = $regex2_mssql.Match($_.Message); $ip = $m.Groups[1].Value; $ip; echo $m; } | Sort-Object | Tee-Object -Variable list | Get-Unique 

   foreach ($ip in $a) { if ($ips -contains $ip) { 
     if (-not ($blacklist -contains $ip)) { 
       $attack_count = ($list | Select-String $ip -SimpleMatch | Measure-Object).count; 
       "Found MSSQL attacking IP on 1433: " + $ip + ", with count: " + $attack_count; 
       if ($attack_count -ge $deny_count) {$blacklist = $blacklist + $ip;} 
     }  
    } 
   } 
 } 

 <# 주석처리. 사용안함. 미 테스트
 #FTP 
 $MyFtpLogFile1 = "";
 $now = (Get-Date).AddMinutes(-5); #check only last 5 mins. 
    #Get-EventLog has built-in switch for EventID, Message, Time, etc. but using any of these it will be VERY slow. 
 $count = (Get-EventLog Security -Newest 1000 | Where-Object {$_.EventID -eq 4625 -and $_.Message -match "Logon Type:\s+8" -and 
             $_.TimeGenerated.CompareTo($now) -gt 0} | Measure-Object).count; 
 if ($count -gt 50) #threshold 
 { 
    $ips = @(); 
    $ips1 = dir "C:\inetpub\logs\LogFiles\FPTSVC2" | Sort-Object -Property LastWriteTime -Descending 
      | select -First 1 | gc | select -Last 200 | where {$_ -match "An\+error\+occured\+during\+the\+authentication\+process."} 
       | Select-String -Pattern "(\d+\.\d+\.\d+\.\d+)" | select -ExpandProperty Matches | select -ExpandProperty value | Group-Object 
       | where {$_.Count -ge 10} | select -ExpandProperty Name; 

    $ips2 = dir "C:\inetpub\logs\LogFiles\FTPSVC3" | Sort-Object -Property LastWriteTime -Descending 
      | select -First 1 | gc | select -Last 200 | where {$_ -match "An\+error\+occured\+during\+the\+authentication\+process."} 
       | Select-String -Pattern "(\d+\.\d+\.\d+\.\d+)" | select -ExpandProperty Matches | select -ExpandProperty value | Group-Object 
       | where {$_.Count -ge 10} | select -ExpandProperty Name; 
    $ips += $ips1; $ips += $ips2; $ips = $ips | where {$_ -ne "10.0.0.1"} | Sort-Object | Get-Unique; 

    foreach ($ip in $ips) { 
      if (-not ($blacklist -contains $ip)) { 
       "Found attacking IP on FTP: " + $ip; 
       $blacklist = $blacklist + $ip; 
      } 
    } 
 } 
 #>

 #Firewall change 

<# $current = (netsh advfirewall firewall show rule name="MY BLACKLIST" | where {$_ -match "RemoteIP"}).replace("RemoteIP:", "").replace(" ","").replace("/255.255.255.255",""); #inside $current there is no \r or \n need remove. foreach ($ip in $blacklist) { if (-not ($current -match $ip) -and -not ($ip -like "10.0.0.*")) {"Adding this IP into firewall blocklist: " + $ip; $c= 'netsh advfirewall firewall set rule name="MY BLACKLIST" new RemoteIP="{0},{1}"' -f $ip, $current; Invoke-Expression $c; } } #> 

 foreach ($ip in $blacklist) { 

   $fw=New-object -comObject HNetCfg.FwPolicy2; # http://blogs.technet.com/b/jamesone/archive/2009/02/18/how-to-manage-the-windows-firewall-settings-with-powershell.aspx 
   $myrule = $fw.Rules | where {$_.Name -eq "MY BLACKLIST"} | select -First 1; # Potential bug here? 

   if (-not ($myrule.RemoteAddresses -match $ip) -and -not ($ip -like "123.123.123.*")) 
     {"Adding this IP into firewall blocklist: " + $ip;  
       $myrule.RemoteAddresses+=(","+$ip); 
       #echo $ip >  C:\BlackListIP.txt
     } 
 } 

 "__________________________________________________________________________________"
 Wait-Event -Timeout $loop_time  #pause 30 secs 

 } # end of top while loop.

해당 코드를 Windows PowerShell ISE 실행하여 붙여넣고 실행하면 된다.
실행하기 전에 자기에게 맡게 IP,포트번호 등의 셋팅을 해야 하며, 윈도우 방화벽 규칙(MY BLACKLIST)이 추가 되어 있어야 한다.

#등록된 IP 전부를 deny 시키는 방화벽 룰 생성.

netsh advfirewall firewall add rule name="MY BLACKLIST" dir=in action=block localip=any remoteip=107.160.158.70

107.160.158.70 아이피는 미국 어태커 IP.

1. 2008에서만 검증 확인. 2012에서도 가능 할 것으로 보임
2. 2003이하에서는 방화벽 동작 방식이 아예 달라서 사용 할 수 가 없다.
3. MY BLACKLIST 이름으로 차단 규칙 추가해야 한다.
4. FTP 스크립트는 테스트 안 해봄
5. 이벤트 로그가 있어야 하며, 현재 사용자가 계속 공격을 시도하고 있어야지 차단목록에 등록된다.
6. 공격자가 접속을 시도하는 상황에서만 차단 동작을 한다.

해당 스크립트를 저장하여 사용할 경우

오류: 이 시스템에서 스크립트를 실행할 수 없으므로 <Script name> 파일을 로드할 수 없습니다. 라는 메시지가 나타난다.

이럴 경우 파워셀을 관리자 권한으로 실행 후 아래의 명령어를 실행하면 된다.

Set-ExecutionPolicy RemoteSigned

출처

auto_blocking.ps1

2012년 7월 25일 수요일

호출 대상이 예외를 throw했습니다 유지관리계획 sql2008

유지관리계획 삭제시 에러발생 '호출 대상이 예외를 throw했습니다'

'sql2008사용

아래처럼 해서 삭제 완료.

http://www.duck.pe.kr/82

http://blog.naver.com/PostView.nhn?blogId=space1079&logNo=20128994032

2011년 12월 30일 금요일

자주 쓰는 mssql 명령어

DB 파일 추가.
EXEC sp_attach_db @dbname = N'kookil',
@filename1 = N'D:\SQLDATA\MSSQL.1\MSSQL\Data\kookil.mdf',
@filename2 = N'D:\SQLDATA\MSSQL.1\MSSQL\Data\kookil.ldf'

유저명정보매치
기존 데이터베이스 사용자를 SQL Server 로그인에 매핑합니다. Microsoft SQL Server의 이후 버전에서는 이 기능이 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 응용 프로그램은 수정하십시오. 대신 ALTER USER를 사용하십시오.

USE [TESTDB]
EXEC sp_change_users_login "Update_One", "유저명", "유저명"

http://msdn.microsoft.com/ko-kr/library/ms174378.aspx

소유자 변경

SP_MSFOREACHTABLE 'sp_changeobjectowner "?", "dbo"'

http://gdbt.tistory.com/58

The Key's Personal store