Monday, March 28, 2011

Restoring an MSSQL DB but user can't log in (SQL Server authentication)

Very common and typical problem when using SQL server auth mode (instead of windows auth mode).

In MSSQL you have a server user entity that is mapped to a specific DB user entity.
When you restore a DB for the first time, you need to create an MSSQL user and map it to your DB's user.
When you restore a DB in the following times (not the first time), you just need to re-map the MSSQL user to the DB user.

Creating an MSSQL user is pretty straight forward - just go to the security node of the server (not of the DB) and create a user with the same name that you have in the DB.

Mapping/Re-Mapping the user can be done using this script (for example we will use the db "mydb" and user "myuser"):

USE mydb
GO
EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser';
GO
EXEC sp_changedbowner 'myuser'
GO

Tuesday, March 15, 2011

How to install an FTP Server on amazon AWS EC2

This seems to be a big issue as people usually waste a lot of time on this.

The problem lies with 2 elements: the security group settings & the ftp server settings.

FTP is not considered a good solution for passing files between EC2 instances and your computer as it is not firewall "friendly" - you can't just open port 21/TCP on the amazon security group settings because the server is actually sitting on a NAT address and when an ftp client connects to an FTP server using PASV mode then the server tells the client which port and which address to use for the data stream (like directory listing).

If you want an alternate easy solution for transferring files between yourself and the EC2 instance then setup a dropbox on both machines - it doesn't get easier than this... :)

If  you still want to set up an FTP server on your EC2 instance then read on...

Typically, people just try to open port 21 and then try to connect and get something similar to:

Status:    Server sent passive reply with unroutable address. Using server address instead.
Command:    MLSD
Response:    425 Can't open data connection.

The solution is (and for this example I will explain how to perform the setting on FileZilla FTP server on windows):
0. Install an FTP server on your amazon instance. For this example i will be using FileZilla FTP server. Just download it(google) and do next->next->next .... until it is finished.

1. Open FileZilla FTP server: Edit-> settings -> Passive mode settings:
 (x) Retrieve external IP from:
       http://ip.filezilla-project.org/ip.php
[ this tells the ftp server to perform a 'whatismyip' and give that to the ftp client, you can also specifically put the address DNS name you use in your remote desktop in the "Use the following IP:", e.g., ec2-6-100-129-60.compute-1.amazonaws.com ]

(x) Use custom port range:
1024-1048

[ these are the ports that will be given to the ftp client - we will open them in the following step in the Firewall ]

2. Amazon security groups: open ports -
   a. Custom TCP rule , Port range:21
   b. Custom TCP rule, Port range:1024-1048

3. Turn off the windows firewall on the EC2 machine (you don't need it - you have the amazon firewall)

That's it, and just as side note, you don't need to use Elastic IP feature for this to work.
Also don't forget to set your client to use PASV mode (which is typically the default).

You can verify this works by reading the answer the server gives the client when it enters PASV mode:

Command:    PASV
Response:    227 Entering Passive Mode (174,149,71,102,4,7)

This response means: "Yo FTP client, use 174.149.71.102 and port 4*256+7=1031"