search

bitwarden / server

Bitwarden infrastructure/backend (API, database, Docker, etc).
https://bitwarden.com
Other
15.37k stars 1.29k forks source link

Using an external mssql database #473

Closed robp2175 closed 5 years ago

robp2175 commented 5 years ago

I would like to use our external database with the docker image. I have been unable to find a how to document for this. Could someone point me in the right direction. Thank you.

kspearrin commented 5 years ago

Just change the mssql connection string in ./bwdata/env/global.override.env to whatever you want.

robp2175 commented 5 years ago

Yes, but I wanted my database to be called bitwarden and it insists on creating a database called vault. There is something else that needs to be change somewhere.

I appreciate the assistance.

globalSettings__sqlServer__connectionString="Data Source=emsrv-msdb1.domain.edu;Initial Catalog=bitwarden;Persist Security Info=False;User ID=bitwarden;Password=P@ssw0rd;MultipleActiveResultSets=False;Connect Timeout=3$
g
kspearrin commented 5 years ago

Database name is not configurable at this time. It must be called "vault".

robp2175 commented 5 years ago

OK, fair enough and thank you for your prompt response. However, this does not function properly. It created the database vault just fine, but no tables have been created.

I have done

./bitwarden.sh update ./bitwarden.sh restart

Thank you

From: Kyle Spearrin [mailto:notifications@github.com] Sent: Tuesday, April 9, 2019 10:07 AM To: bitwarden/server server@noreply.github.com Cc: Pelletier, Robert rpelletier@emcc.edu; Author author@noreply.github.com Subject: Re: [bitwarden/server] Using an external mssql database (#473)

Database name is not configurable at this time. It must be called "vault".

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/bitwarden/server/issues/473#issuecomment-481266989, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AIbEswuFVNh5WGLLZhu6KQp5m2zYhTDQks5vfJ6VgaJpZM4ckkWI.

kspearrin commented 5 years ago

Try initializing the database with ./bitwarden.sh updatedb

robp2175 commented 5 years ago

It looks like my last replay never made it here. Sorry for the late response. I have tried ./bitwarden.sh updatedb several times, but all it does is create an empty database.

abctaylor commented 5 years ago

@robp2175 @kspearrin Hi Kyle, I am interested in the same (using a full-blown SQL Server to do the magic).

Result of running updatedb: Migrating database. Migration failed. Database update complete root@dev-pwd-ldn-3:~#

Connection string (password removed...): globalSettings__sqlServer__connectionString="Data Source=tcp:dev-sql-ldn-1.core.arcza.net,1433;Initial Catalog=vault;Persist Security Info=False;User ID=sql.bitwarden;Password=xxxxxxxxxxxxxxxxxxxxxxxxx;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=True"

Does anything look strange? Bitwarden GUI will report An unhandled server error has occurred.

Thanks, Andrew

abctaylor commented 5 years ago

Server looks good and other databases are running fine. Bitwarden has very liberal access on this db...

Also note in the screenshot - it has made no tables in db 'vault'. Suggests a possible auth issue. Password has no ';' symbols etc in (just alphanumeric). Server is 100% live.

bitwarden mssql migration issue
kspearrin commented 5 years ago

Looks to me like a connection issue still. Did you create the database vault or was that automatically created when you ran updatedb?

abctaylor commented 5 years ago

Thx for fast reply. Made 'vault' in SQL Server myself before running updatedb.

kspearrin commented 5 years ago

Yes, sounds like a connection issue then. What are the logs in ./bedata/log/Api showing from the uncaught server error?

abctaylor commented 5 years ago

Logs too big to view remotely on my iPhone's ssh client. Looks v detailed and I will try and re-break it to catch the error and revert later in a few hours. Thanks for pointing me to this.

If I find the problem, I'll post and close case.

abctaylor commented 5 years ago

Resolved:

  1. Create user xxx in SSMS
  2. Create database vault
  3. Assign datareader and datawriter roles (Image 1)
  4. ./bitwarden.sh updatedb
  5. Refresh the database's tables in SSMS to confirm (image 2)

Image 1 image

Image 2 image

abctaylor commented 5 years ago

I spoke too soon :(

Indeed all tables have now been created by Bitwarden on the SQL Server, however I am having trouble migrating @robp2175. e.g. see below - updatedb says a migration was successful.

  1. I rebuild the docker instance with the containerised mssql, everything fine
  2. I change the config to now point to the external SQL Server
  3. updatedb

As you can see below, the migration didn't work and there are no users (or anything else either) in the external SQL Server.

image

kspearrin commented 5 years ago

The migration does create any new users. Just the schema. You now need to register a user account on the web vault.

abctaylor commented 5 years ago

Indeed that works (SSMS shows new user) but why can't bitwarden migrate the whole vault (of all users) to the external mssql?

kspearrin commented 5 years ago

updatedb is a schema migration. It does not migrate data. To migrate data you would need to restore one of the backups from ./bwdata/mssql/backups

abctaylor commented 5 years ago

Understood, thank you.

robp2175 commented 5 years ago

None of this worked for me. I followed the exact same steps as @abctaylor and I am still getting migration failed. I made my user sysadmin and still get migration failed. I also only have alphanumeric password. My api log only shows this

2019-04-25 13:00:00.220 +00:00 [Information] Starting job "AliveJob" at 04/25/2019 13:00:00.
2019-04-25 13:00:00.307 +00:00 [Information] It's alive!
2019-04-25 13:00:00.321 +00:00 [Information] Finished job "AliveJob" at 04/25/2019 13:00:00.

My connection string looks exactly like @abctaylor (except of course different server) It does not matter whether I create the database vault manually or let the script do it, no tables are created and I get migration failed. 

Docker version 18.09.5, build e8ff056
docker-compose version 1.21.2, build a133471

1.30.1: Pulling from bitwarden/setup
Digest: sha256:018d3be93bc5c8c3c79586cd92461b324d692f0f76ece331f87294abd4de109d
Status: Image is up to date for bitwarden/setup:1.30.1

Migrating database.
Migration failed.
Database update complete
abctaylor commented 5 years ago

In SSMS try login using SQL authentication (not windows) and see if the gui lets you in. Then confirm if you can expand the tables (there will initially be just four - System Tables, FileTables, External Tables and Graph Tables). Can you expand this or does SSMS give you a permission error?

robp2175 commented 5 years ago

I can login and expand vault without issue. No errors.

abctaylor commented 5 years ago

Is your password alphanumeric? Eg no weird symbols?

robp2175 commented 5 years ago

Yes, alphanumeric. I changed it after I ready your post. Deleted the database and did a complete reinstall of bitwarden, then tried again with the same issue.

abctaylor commented 5 years ago

When you did your reinstall, did you first set it up using the mssql docker? This is what I did, then while live I edited global config in bwdata/env with the external server's connection string. Then when that was still live and running, I did the updatedb, rebuild, update and updateself etc (I forgot the order). Tried rebuild?

Then I didn't bother exporting/importing the database to move it across to the external sql so I just used the running app to export all passwords in csv and then reimport prior to changing things.

abctaylor commented 5 years ago

But just to be explicit, you know it's worked when Bitwarden makes its own tables. I made the vault database for it using my admin account on the sql server first.

Also I don't know your environment but if you are firewalling the box running Bitwarden make sure port 1433 is open...

robp2175 commented 5 years ago

So, I just did another completely clean install. Starting it up with default mssql container settings, changed the connection string in env file. Ran updatedb...and migration failed.

I have tried rebuild, update, updateself...doing all of these things while standing on my head and spinning plates, migration failed, migration failed

Firewall port is open (it creates the database without issue, although I have manually created the database for the last few tests).

what version of sql are you on? I am on 2014

robp2175 commented 5 years ago

I have tried looking for some sql scripts in the repo to see if I can manually create the entire schema, but can't find anything. As much I do not want to do it, I am close to just going with the mssql container :-(

This just doesnt make any sense. I have never had anything create a database but fail to create the tables. And I can not find any logs giving me an idea of what is actually failing. Flying a little blind.

abctaylor commented 5 years ago

2017 developer edition

Have you tried wiping bwdata, doing a ./bitwarden.sh install and before doing ./bitwarden.sh start you change the connection string to use the external SQL? Then start it, create the first user etc.

Also if you don't mind, paste your config.

For me having external DB is mandatory as I want a HA setup where if one data centre fails there is redundancy:

DC 1 pwd server 1 <--> sql 1

~~ SQL MIRRORING ~~

pwd server 2 <--> sql 2

This is almost fully working for me now - I just need to mirror the SQL with always on high availability.

abctaylor commented 5 years ago

Also check your sql user is a) a local user on the box not a domain user (probably doesn't matter) and b) has exactly datareader, datawriter and public roles assigned to it? Also don't bother with bitwarden making the db, do it yourself in SSMS like I did.

abctaylor commented 5 years ago

*local user on box = local sql login (not a windows account or anything) with permissions on the vault database you make manually

robp2175 commented 5 years ago

of course

globalSettings__baseServiceUri__vault=https://bitwarden.domain.edu
globalSettings__baseServiceUri__api=https://bitwarden.domain.edu/api
globalSettings__baseServiceUri__identity=https://bitwarden.domain.edu/identity
globalSettings__baseServiceUri__admin=https://bitwarden.domain.edu/admin
globalSettings__baseServiceUri__notifications=https://bitwarden.domain.edu/notifications
globalSettings__sqlServer__connectionString="Data Source=tcp:emsrv-msdb1.domain.edu,1433;Initial Catalog=vault;Persist Security Info=False;User ID=sql.bitwarden;Password=hidden;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=True"
globalSettings__identityServer__certificatePassword=ZhToheZdJaAwSOo4LAMQRjkGmaldGVWp
globalSettings__attachment__baseDirectory=/etc/bitwarden/core/attachments
globalSettings__attachment__baseUrl=https://bitwarden.domain.edu/attachments
globalSettings__dataProtection__directory=/etc/bitwarden/core/aspnet-dataprotection
globalSettings__logDirectory=/etc/bitwarden/logs
globalSettings__licenseDirectory=/etc/bitwarden/core/licenses
globalSettings__internalIdentityKey=hidden
globalSettings__duo__aKey=hidden
globalSettings__installation__id=hidden
globalSettings__installation__key=hidden
globalSettings__yubico__clientId=REPLACE
globalSettings__yubico__key=REPLACE
globalSettings__mail__replyToEmail=no-reply@bitwarden.domain.edu
globalSettings__mail__smtp__host=REPLACE
globalSettings__mail__smtp__port=587
globalSettings__mail__smtp__ssl=false
globalSettings__mail__smtp__username=REPLACE
globalSettings__mail__smtp__password=REPLACE
globalSettings__disableUserRegistration=false
adminSettings__admins=
abctaylor commented 5 years ago

Does your box running bitwarden resolve emsrv-msdb1.domain.edu to the right IP? Can you ping that IP?

robp2175 commented 5 years ago

I wiped everything again. Ran install and then changed the connection string. Then started bitwarden for the first time. Tried to create first user and of course got unhandled exception, likely because the tables were not created and sure enough they were not.

Yes, I can ping the server. This is a production sql server that we use for many DBs on campus. I have installed and configured numerous other DBs on this machine and have never had an issue. There is a bug here somewhere. I am not new at this, although I am always open to accepting that I may have missed something. However, in this case I feel I have tried every possible scenario and I keep getting the same error. @abctaylor I greatly appreciate your help, but I am going to need to throw in the towel for today and take a step back. Someone needs to provide some guidance regarding what log would show us what is failing because otherwise I am going to just be chasing my tail.

Again, @abctaylor, thanks a ton for your time.

abctaylor commented 5 years ago

Most welcome, happy to help. I find it strange I can get it running but not you so please post if you find a resolution to your issue as I am very curious now. I want to know before I take my setup to prod.

fr-ashvini commented 4 years ago

2017 developer edition

Have you tried wiping bwdata, doing a ./bitwarden.sh install and before doing ./bitwarden.sh start you change the connection string to use the external SQL? Then start it, create the first user etc.

Also if you don't mind, paste your config.

For me having external DB is mandatory as I want a HA setup where if one data centre fails there is redundancy:

DC 1 pwd server 1 <--> sql 1

~~ SQL MIRRORING ~~

pwd server 2 <--> sql 2

This is almost fully working for me now - I just need to mirror the SQL with always on high availability.

Hi Iam as well doing something similar. i have two bitwarden instances connected to respective external mssql, db got updated. but how to proceed with sql mirroring between them .Could you share some details. @kspearrin @abctaylor

abctaylor commented 4 years ago

@fr-ashvini there are lots of ways to do this; I suggest you search "always on high availability" which SQL Server will do. You may also need to look at failover clustering.

fr-ashvini commented 4 years ago

@abctaylor : Thanks for reply.

abctaylor commented 4 years ago

To have Bitwarden run across two databases on different servers, you would want to use Always On High Availability - you will still run Bitwarden server but you will have an external database.

You then tell Bitwarden to connect to something called "availability group listener" - which is like a virtual server that presents the two SQL Servers you have running Always On HA. These two SQL Servers will need to run on Windows Server machines with failover clustering. You cannot use the dockerized mssql that ships with Bitwarden.

If you use AOHA, it will work quite well with Bitwarden but requires a lot of time investment.

fr-ashvini commented 4 years ago

Hi Hope my question is clear, Say two bitwarden server in two different AWS ec2 instance. Both bitwarden servers are connected to same external aws ec2 mssql server.

so upon updatedb command from bitwarden server.

  1. Will the mssql server be created with two vault? or
  2. Within same vault, two separate table to store the respective User data?

How will it be proceeded?

kspearrin commented 4 years ago

@fr-ashvini if you use the same database both severs will serve up the same data.

fr-ashvini commented 4 years ago

@kspearrin Yes it should, but both severs arent serving up the same data Server 1 has its own data, Server 2 has it. they arent reflecting or sharing the same space. I checked in MSSQL server, theres only one vault created though.

fr-ashvini commented 4 years ago

@kspearrin How to confirm if the BItwarden server is actually connected to the external DB?

kspearrin commented 4 years ago

Create a user account and then query the User table on the external DB to see if that user account is there.

fr-ashvini commented 4 years ago

Hi problem resolved. Thank you for constant replies to queries.

thusted-oss commented 4 years ago

Hi, I am currently working on a project which involves Bitwarden connecting to an external mssql server. I read through this issue-thread and tried various versions of configurations to connect to the external database.

Environment: HN: docker-host-t01 == Docker Host [Ubuntu 20.04] running Portainer and Bitwarden HN: vm-sql-server01 == MS SQL-Server 2016 with 3 Instances [INSTANCEA, INSTENACEB, INSTANCEC]

Both are on the same .100.XYZ Network. Resolving the hostname and ping works. The SQL-Server is part of a domain - the docker-host isn't.

Questions:

  1. How to I specify the instance name in the connection String? 1.1 -> I found a hint in the documentation of pyodbc (Notes No. 2) that the Microsoft ODBC drivers for Linux cannot resolve instance names. Does this apply to bitwarden as well? https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Linux
  2. Is it possible to use a domain-user or only a SQL-User, if so - how is the domain defined in the config (ex. username@domain.com or domain\username)? 2.1 -> The user has the same permissions (db_datareader & db_datawriter) as stated by @abctaylor in one of the earlier replies.

Example one of the configs i tried:

globalSettings__sqlServer__connectionString="Data Source=tcp:192.168.100.176/INSTANCEB,1433;Initial Catalog=vault;Persist Security Info=False;User ID=svc_bitwarden-app@domain.com;Password=Placeholder123; MultipleActiveResultSets=False;Connect Timeout=30; Encrypt=True;TrustServerCertificate=True"
globalSettings__identityServer__certificatePassword=uUZnmS6hrOiQWqKEfOKXAaXIFyA1VgI3
globalSettings__attachment__baseDirectory=/etc/bitwarden/core/attachments
globalSettings__attachment__baseUrl=https://pam-app-t-01.domain.com/attachments
globalSettings__dataProtection__directory=/etc/bitwarden/core/aspnet-dataprotection
globalSettings__logDirectory=/etc/bitwarden/logs
globalSettings__licenseDirectory=/etc/bitwarden/core/licenses
globalSettings__internalIdentityKey=1RPa8h6tcCQKsC0DcHi58U1N9m1eOxNkYi1yYdliXgQpCBAxxKOHbKSdSWFxxZ4A
globalSettings__duo__aKey=KUuqhxRsebNJOPB89hsNFlD4HGXs1ltQPh89vvrAO2FQKVytWpJHlCqHHgl2NosT
globalSettings__installation__id=XXXXXXXXXX
globalSettings__installation__key=XXXXXXXXXX
globalSettings__yubico__clientId=REPLACE
globalSettings__yubico__key=REPLACE
globalSettings__mail__replyToEmail=no-reply@hostname.domain.com
globalSettings__mail__smtp__host=REPLACE
globalSettings__mail__smtp__port=587
globalSettings__mail__smtp__ssl=false
globalSettings__mail__smtp__username=REPLACE
globalSettings__mail__smtp__password=REPLACE
globalSettings__disableUserRegistration=false
globalSettings__hibpApiKey=REPLACE
adminSettings__admins=

Error i get:

Unhandled exception. System.Data.SqlClient.SqlException (0x80131904): 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: TCP Provider, error: 25 - Connection string is not valid)
   at Bit.Setup.Program.MigrateDatabase(Int32 attempt) in /home/appveyor/projects/server/util/Setup/Program.cs:line 193
   at Bit.Setup.Program.Update() in /home/appveyor/projects/server/util/Setup/Program.cs:line 140
   at Bit.Setup.Program.Main(String[] args) in /home/appveyor/projects/server/util/Setup/Program.cs:line 65
ClientConnectionId:00000000-0000-0000-0000-000000000000
kspearrin commented 4 years ago

Not sure if this is the current issue at hand, but you need sql server 2017 or greater. Looks like you have 2012.

thusted-oss commented 4 years ago

Not sure if this is the current issue at hand, but you need sql server 2017 or greater. Looks like you have 2012.

Hi, thanks for the fast reply. That was an error on my side. The Windows Server Version the SQL Server is running on is 2012. The SQL-Server itself is Version 2016 (SP2 CU11) image

flomader commented 1 year ago

I had a similar issue. The root cause was that the bitwarden/setup:2022.10.0 container did not have network access. I resolved it by deleting --network container:$MSSQL_ID from the docker run command in the updateDatabase() function in file bwdata/scripts/run.sh