Self-Hosted with External MSSQL: Procedure or function CipherDetails_Create has too many arguments specified.

[HeyITGuyFixIt]

Steps To Reproduce

1. Go to Vault
2. Click on plus icon
3. Enter any information or just a name
4. Click on Save

Expected Result

Editing and creating records should save without error.

Actual Result

Client throws error saying an unhandled server error has occurred, and the bitwarden-api container throws this error:

2023-10-31T13:44:55.428468786Z fail: Bit.Api.Utilities.ExceptionHandlerFilterAttribute[0]
2023-10-31T13:44:55.428531410Z       => SpanId:98546295d6cfa8a3, TraceId:867db5f1509482066aabf3671e768491, ParentId:0000000000000000 => ConnectionId:0HMUPOOHTB48A => RequestPath:/ciphers RequestId:0HMUPOOHTB48A:00000002 => Bit.Api.Vault.Controllers.CiphersController.Post (Api)
2023-10-31T13:44:55.428539793Z       Procedure or function CipherDetails_Create has too many arguments specified.
2023-10-31T13:44:55.428543049Z       Microsoft.Data.SqlClient.SqlException (0x80131904): Procedure or function CipherDetails_Create has too many arguments specified.
2023-10-31T13:44:55.428545461Z          at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
2023-10-31T13:44:55.428547693Z          at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
2023-10-31T13:44:55.428549919Z          at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
2023-10-31T13:44:55.428552083Z          at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
2023-10-31T13:44:55.428554485Z          at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
2023-10-31T13:44:55.428556870Z          at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
2023-10-31T13:44:55.428559008Z          at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
2023-10-31T13:44:55.428561110Z          at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
2023-10-31T13:44:55.428563152Z          at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
2023-10-31T13:44:55.428566525Z          at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
2023-10-31T13:44:55.428569086Z       --- End of stack trace from previous location ---
2023-10-31T13:44:55.428581457Z          at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) in /_/Dapper/SqlMapper.Async.cs:line 646
2023-10-31T13:44:55.428584395Z          at Bit.Infrastructure.Dapper.Vault.Repositories.CipherRepository.CreateAsync(CipherDetails cipher) in /home/runner/work/server/server/src/Infrastructure.Dapper/Vault/Repositories/CipherRepository.cs:line 137
2023-10-31T13:44:55.428586823Z          at Bit.Core.Vault.Services.CipherService.SaveDetailsAsync(CipherDetails cipher, Guid savingUserId, Nullable`1 lastKnownRevisionDate, IEnumerable`1 collectionIds, Boolean skipPermissionCheck) in /home/runner/work/server/server/src/Core/Vault/Services/Implementations/CipherService.cs:line 142
2023-10-31T13:44:55.428589468Z          at Bit.Api.Vault.Controllers.CiphersController.Post(CipherRequestModel model) in /home/runner/work/server/server/src/Api/Vault/Controllers/CiphersController.cs:line 134
2023-10-31T13:44:55.428591699Z          at lambda_method62(Closure , Object )
2023-10-31T13:44:55.428593723Z          at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
2023-10-31T13:44:55.428595967Z          at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
2023-10-31T13:44:55.428598238Z          at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
2023-10-31T13:44:55.428600461Z          at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
2023-10-31T13:44:55.428602488Z          at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
2023-10-31T13:44:55.428605431Z          at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
2023-10-31T13:44:55.428607965Z          at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
2023-10-31T13:44:55.428610245Z       ClientConnectionId:6048d691-5317-444a-8880-e3c3e5432fef
2023-10-31T13:44:55.428612196Z       Error Number:8144,State:2,Class:16

Screenshots or Videos

No response

Additional Context

No response

Build Version

2023.9.1

Environment

Self-Hosted

Environment Details

• bitwarden.sh version 2023.9.1
• Docker version 24.0.5, build ced0996
• Docker Compose version v2.20.2
• Host is Ubuntu Server with Linux kernel 5.15.0-87-generic in a Virtual Machine running on Windows 10 Pro 22H2.
• The same Windows 10 machine has the Microsoft SQL Server 2019 (15.0.4326)

Issue Tracking Info

• I understand that work is tracked outside of Github. A PR will be linked to this issue should one be opened to address it, but Bitwarden doesn't use fields like "assigned", "milestone", or "project" to track progress.

[atjbramley]

Hi @HeyITGuyFixIt ,

I am unable to reproduce this issue (as I do not have access to SQL Server), so it has been escalated for further investigation. If you have more information that can help us, please add it below.

Thanks!

[ruizhii]

Based on my findings, the stored procedure CipherDetails_Create has been updated recently, in the last update 2021-05-04_00_CipherPasswordPromptFixed.sql, it accepts only 17 parameters whereas in a more recent update, 2023-09-27_00_CipherKeyUpdate.sql there's now 18 parameters, which is same as total count of properties for Bit.Core.Vault.Models.Data.CipherDetails class.

Based on the above, I would assume the database for some reason, might not have applied the latest migration properly, while the software is already up to date which are supplying 18 parameters when calling the stored procedure, and it can only accept 17. I'm not sure exactly how the migration gets triggered to start and include the appropriate files yet, but hopefully that provides a direction for debugging. If I'm not mistaken, there should be a log too for the migration, so maybe we can find some useful information over there.

[HeyITGuyFixIt]

@ruizhii Based on your response, I tried to run bitwarden.sh updatedb but I got an error:

Unhandled exception. Microsoft.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: 35 - An internal exception was caught)
 ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (00000001, 11): Resource temporarily unavailable

Not sure yet why it can't connect, but if it couldn't connect when I had tried to update everything earlier then that would explain this issue.

[HeyITGuyFixIt]

Looks like I can't connect to anything from the bitwarden-mssql container. I tried to wget https://1.1.1.1, and that times out. I tried to connect to the sql server using the mssqlcmd command and it times out as well. Looks like a network issue with this container.

[HeyITGuyFixIt]

Honestly, not sure which container is responsible for running the updatedb command.

[ruizhii]

@HeyITGuyFixIt may I know if the bitwarden.sh script you mentioned is the one from bitwarden/self-host repo? I saw that in this repo the script contains an instruction pointing to that one.

[HeyITGuyFixIt]

@ruizhii it should be. It's version 2023.9.1. I'm trying the new update to see if that fixes things.

[HeyITGuyFixIt]

Still not working after the update. Using the server's FQDN I get the "Resource temporarily unavailable" error, but switching to its IP I get this:

Unhandled exception. Microsoft.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: 40 - Could not open a connection to SQL Server: Could not open a connection to SQL Server)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Bit.Migrator.DbMigrator.MigrateDatabase(Boolean enableLogging, Boolean repeatable, String folderName, CancellationToken cancellationToken) in /home/runner/work/server/server/util/Migrator/DbMigrator.cs:line 82
   at Bit.Migrator.DbMigrator.MigrateMsSqlDatabaseWithRetries(Boolean enableLogging, Boolean repeatable, String folderName, CancellationToken cancellationToken) in /home/runner/work/server/server/util/Migrator/DbMigrator.cs:line 38
   at Bit.Setup.Program.MigrateDatabase(Int32 attempt) in /home/runner/work/server/server/util/Setup/Program.cs:line 194
   at Bit.Setup.Program.Update() in /home/runner/work/server/server/util/Setup/Program.cs:line 158
   at Bit.Setup.Program.Main(String[] args) in /home/runner/work/server/server/util/Setup/Program.cs:line 56

[HeyITGuyFixIt]

I can confirm that the bitwarden-admin container can get to the server by both FQDN and IP. I can't find a way to try connecting to the sql server from that container.

[HeyITGuyFixIt]

I changed the config to use a different Catalog. It recreated that database and was able to resolve the initial issue. I retried ./bitwarden.sh updatedb but it still couldn't connect to the sql server. That seems to be the underlying issue here.

[ruizhii]

I'm glad it works the other way @HeyITGuyFixIt, but I really have no idea from here on. Let's see if someone else will respond to this issue, hopefully we'll get some good help.