# SqlMembershipProvider

Read and authenticate against ASP.NET Membership Microsoft SQL Server databases.

Do you have an ASP.NET app that uses the built-in Membership Provider that
stores users, passwords, roles, and profiles in a Microsoft SQL Server database?

This library lets your Elixir code talk to a Membership Provider database to lookup users, their profile information roles they belong to, and authenticate their passwords.

This library uses [Tds](https://hex.pm/packages/tds) to connect to a Microsoft SQL Server database.

## Usage

```elixir
iex(1)> {:ok, pid} = Tds.start_link(hostname: "localhost", username: "", password: "", database: "", port: 1433)
{:ok, #PID<0.236.0>}

iex(2)> user = SqlMembershipProvider.User.find_by_user_name(pid, "JohnDoe")
%SqlMembershipProvider.User{
  ...
}

iex(3)> membership = SqlMembershipProvider.Membership.find_by_user_id(pid, user.user_id)
%SqlMembershipProvider.Membership{
  ...
}

iex(4)> SqlMembershipProvider.Membership.is_password_valid?(membership, "password!")
true

iex(5)> profile = SqlMembershipProvider.Profile.find_by_user_id(pid, user.user_id)
%SqlMembershipProvider.Profile{
  ...
}

iex(6)> SqlMembershipProvider.Profile.properties(profile)
%{
  "FirstName" => "John",
  "LastName" => "Doe",
  ...
}

iex(7)> roles = SqlMembershipProvider.Role.find_by_user_id(pid, user.user_id)
[
  %SqlMembershipProvider.Role{
    role_name: "Administrators",
    ...
  }
]
```

## Running Tests

Clone the repo and fetch its dependencies:

```
$ git clone https://gitlab.com/horse-racing-labs/sql_membership_provider.git
$ cd sql_membership_provider
$ mix deps.get
```

[Docker Compose](https://docs.docker.com/compose/install/) is required to run a
sample SQL Server database to run integration tests against.

```
$ docker-compose up
$ ./setup-test-database.sh
$ mix test
```

## Future Work

Once Tds works with Ecto 3.x, this will migrate to using Ecto.

## Help Wanted

* Only the SHA1 password format is supported. Support should be added for [the
  remaining password formats](https://docs.microsoft.com/en-us/dotnet/api/system.web.security.membershippasswordformat?view=netframework-4.8).
* Add support for reading binary properties from profiles