Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Create SQL data base form error #3595

Open
lnicolae opened this issue Dec 25, 2023 · 14 comments
Open

Create SQL data base form error #3595

lnicolae opened this issue Dec 25, 2023 · 14 comments

Comments

@lnicolae
Copy link

  1. create a new SQL data base with a new sql connection string. (but be sure to be a wrong string), and fill all fields (included password!!)
  2. try to create the data base (instal button)
  3. error, with sql error in detals box.
  4. corect SQL connection string
  5. Try to create the data base (instal button)
  6. error, with incorect Password

this is a logiccal error in code

@sbwalker
Copy link
Member

@lnicolae can you please share the exact error message or a screen shot so that it is easier to understand the specifics of the error?

@lnicolae
Copy link
Author

lnicolae commented Dec 27, 2023

image
The connection string is:
Data Source=HOSTS\MSSQLSERVER01;Initial Catalog=ProdMec;User;ID=test;Password=Test1;Encrypt=False
Connection string work in another .NET app
The error message is:
An Error Occurred Creating The Database. This Is Usually Related To Your User Not Having Sufficient Rights To Perform This Operation. Please Note That You Can Also Create The Database Manually Prior To Initiating The Install Wizard. Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'test'. at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) 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, Func3 accessTokenCallback) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.b__0(DateTime giveUp) at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_02.b__0(DbContext _, TState s) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func2 operation, Func2 verifySucceeded) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists() at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated() at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated() at Oqtane.Infrastructure.DatabaseManager.CreateDatabase(InstallConfig install) in D:\oqtane\oqtane.framework-dev\Oqtane.Server\Infrastructure\DatabaseManager.cs:line 230 ClientConnectionId:ea9722a1-b596-4d19-94ba-e60d08767fda Error Number:18456,State:1,Class:14

NOTE:
Be carrefull that connection string HAS AN DATA BASE ProdMec
Without DATA BASE a new DATA BASE IS CREATED

SECOND ERROR:
This error occurre only IF FIRST ERROR OCCURE, else a new Data Base is created

Remove DATA BASE from connection string, will receive another error

@sbwalker
Copy link
Member

sbwalker commented Jan 1, 2024

@lnicolae - are you using the source code directly from GitHub? If so, did you open the Oqtane.sln file in Visual Studio and build the solution (Build / Rebuild Solution) before running the application?

@lnicolae
Copy link
Author

lnicolae commented Jan 1, 2024

yes, alway use latest code from GIT in VS2022 Preview (last version), alway Rebuild (not use Build)
The connection string WITHOUT parameter "Initial Catalog" work OK. Do not miss that.
Also, the connection string is write in config file even the data base fails to initialize. (create). This is a LOGICAL ERROR in code.

@sbwalker
Copy link
Member

sbwalker commented Jan 2, 2024

@lnicolae you posted a screen shot of the first error... but it is actually the second error related to the Invalid Password which you are saying is the issue - correct? I am assuming the actual message you are seeing for the invalid password is "The Password Provided Does Not Meet The Complexity Policy. Passwords Must Be At Least 6 Characters In Length And Contain Uppercase, Lowercase, Numeric, And Punctuation Characters." - correct?

@lnicolae
Copy link
Author

lnicolae commented Jan 2, 2024

on my first post you have 6 steps to reproduce that error/errors. On my case, the problem that occurre is not so obviously, because I use a custom user test, and my Microsoft acocunt is disable, and possible an error on Microsoft code with this settings. But fallow the 6 steps. The error is there. You need only to fallow the steps. That is all

@curlyfro
Copy link

curlyfro commented Jan 5, 2024

i'm getting the same error. i downloaded v5.0.1. i'm running sql server from a container -- my db server is localhost, 1433. that shouldn't be a problem. same error with localdb.

i'm going to try downgrading to an earlier version. i downloaded and installed a fresh copy a few months without issues. not sure why i'm getting this new error.

@sbwalker
Copy link
Member

sbwalker commented Jan 5, 2024

@curlyfro what exactly did you download - more details are always helpful.

@curlyfro
Copy link

curlyfro commented Jan 5, 2024

sure no problem.
i downloaded v5.0.1 source code (zip) from github. i loaded the Oqtane solution and i started debugging the server project. when the installer came up, i submitted a valid form -- validated password, working sql creds. i didn't get any serverside error -- just clientside.

An Error Occurred Creating The Database. This Is Usually Related To Your User Not Having Sufficient Rights To Perform This Operation. Please Note That You Can Also Create The Database Manually Prior To Initiating The Install Wizard. System.ArgumentNullException: Value cannot be null. (Parameter 'type') at System.ArgumentNullException.Throw(String paramName) at System.Activator.CreateInstance(Type type, Boolean nonPublic, Boolean wrapExceptions) at Oqtane.Infrastructure.DatabaseManager.CreateDatabase(InstallConfig install) in D:\oqtane.framework-5.0.1\oqtane.framework-5.0.1\Oqtane.Server\Infrastructure\DatabaseManager.cs:line 220

@curlyfro
Copy link

curlyfro commented Jan 6, 2024

i downloaded v4.0.6 source code. same everything. worked.

@thabaum
Copy link
Contributor

thabaum commented Jan 6, 2024

@curlyfro Also be sure your configuration of your SQL server allows remote access.

Main issues that bring up this error:

Server Address Issue: Wrong information or format, for your case you should just need to enter "localhost"

Server Configuration Issue: Remote Access is not allowed.

Server Database Permissions: Oqtane will create the database for you using integrated security since permissions are local to your the user running the app. For manual creation make sure the database is created with your SQL User as DB Owner permissions mapped to it.

Security Credentials: Sometimes you set a password one way, and by golly you are one character off when trying to set this password. Be sure they are typed exactly, for testing you can use notepad to copy and paste this while creating the user in SQL Server and also back into the Oqtane Server setup form.

Biggest problem showing up here in issues and discussions has been people not performing a build or rebuild on the entire Oqtane Framework solution so all the projects inlcluding the database projects get compiled prior to running (debugging) the application. I used rebuild not sure if that made a difference.

The error you are having is similar to the ones I had earlier when testing, but actually this is different when reviewing it closer as I was using the wrong server address as I was using my machine name and some other settings from connection string formats I had used in the past... low and behold "localhost" was all I needed.

I have been using localdb for long time now, I had to do a refresher on the SQL Server on my IDE but I can pretty much connect and setup any type of database or server so it wasn't long before I figured it out. I did feel like my settings where correct and all was right so I was going to create an issue, but then I found the problem and all related to those outlined here.

I have now tested connecting using SQL server security both custom and integrated with a very recent dev branch and Visual Studio 2022 (Not Preview as one mentioned before). I was successful in both attempts as long as I clicked the build button in Visual Studio prior to running debug launching the application.

After testing my environment I did the following to create a SQL Database connection:

image

I was able to successfully connect to the database server all times as long as I built the solution projects first.

This was my workflow:

  1. Downloaded the latest dev branch
  2. Open the Oqtane Solution in Visual Studio 2022 Community Edition or better (not preview)
  3. Build The Entire Solution Collection Of Projects Once Prior To Hitting F5 or Running Debug Or Release Modes.
  4. Run Oqtane.Server in debug mode
  5. Entered SQL Server configuration settings and credentials as shown in image.

SSL Encryption Settings maybe something to review.

Any settings off from what I am showing in my attempt to reproduce this issue?

Always happy to try and help, Cheers!

@thabaum
Copy link
Contributor

thabaum commented Jan 11, 2024

working with IIS on Windows 2022 server and Windows 11 trying to install I get this same exact message.

I think I may have found some information worth sharing.
If I try to go to the site using HTTPS SSL I get only an error in the log file that relates to it.
If I disable SSL on the site taking away its binding on IIS I can then navigate to non-SSL HTTP version which then I get the installation screen.
I then try to enter SQL strings and get all kinds of errors similar to the ones being discussed.

However running in Visual Studio Localhost:44357 or whatever... it works ok.

I was using the release package install.

So I was convinced it should work but it wasnt... look a little deeper and I forgot to set my sql server to allow sql server and windows authentication.

image

But this does not explain the issue with connecting via SSL using the IIS Developer Certificate. Some reason Oqtane does not like it and will not load the setup page.

Again it was a configuration issue with my new SQL server instance I had to set, and generally always do. That allowed user authentication using self-signed encrypted custom settings.

But again not sure this is the case but worth mentioning.

I want to figure out why I cannot setup using IIS and SSL. As soon as I switch the site to SSL it wont load anymore. Maybe a different but relating issue?

This is generated in the Content\Log\error.txt file when attempting to access the site using SSL Self Signed Certificates, not sure if this issue still happens with signed certificates:

[2024-01-11 04:21:39+00:00] [Error] Connection ID "18158513706953080839", Request ID "40000008-0002-fc00-b63f-84710c7967bb": An unhandled exception was thrown by the application.    at System.Net.Http.ConnectHelper.EstablishSslConnectionAsync(SslClientAuthenticationOptions sslOptions, HttpRequestMessage request, Boolean async, Stream stream, CancellationToken cancellationToken)
   at System.Net.Http.HttpConnectionPool.ConnectAsync(HttpRequestMessage request, Boolean async, CancellationToken cancellationToken)
   at System.Net.Http.HttpConnectionPool.CreateHttp11ConnectionAsync(HttpRequestMessage request, Boolean async, CancellationToken cancellationToken)
   at System.Net.Http.HttpConnectionPool.AddHttp11ConnectionAsync(QueueItem queueItem)
   at System.Threading.Tasks.TaskCompletionSourceWithCancellation`1.WaitWithCancellationAsync(CancellationToken cancellationToken)
   at System.Net.Http.HttpConnectionPool.SendWithVersionDetectionAndRetryAsync(HttpRequestMessage request, Boolean async, Boolean doRequestAuth, CancellationToken cancellationToken)
   at System.Net.Http.DiagnosticsHandler.SendAsyncCore(HttpRequestMessage request, Boolean async, CancellationToken cancellationToken)
   at System.Net.Http.RedirectHandler.SendAsync(HttpRequestMessage request, Boolean async, CancellationToken cancellationToken)
   at System.Net.Http.HttpClient.<SendAsync>g__Core|83_0(HttpRequestMessage request, HttpCompletionOption completionOption, CancellationTokenSource cts, Boolean disposeCts, CancellationTokenSource pendingRequestsCts, CancellationToken originalCancellationToken)
   at Oqtane.Services.ServiceBase.GetJsonAsync[T](String uri) in C:\Source\Projects\oqtane.framework\Oqtane.Client\Services\ServiceBase.cs:line 143
   at Oqtane.Services.InstallationService.IsInstalled() in C:\Source\Projects\oqtane.framework\Oqtane.Client\Services\InstallationService.cs:line 42
   at Oqtane.App.OnParametersSetAsync() in C:\Source\Projects\oqtane.framework\Oqtane.Client\App.razor:line 68
   at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
   at Microsoft.AspNetCore.Components.ComponentBase.RunInitAndSetParametersAsync()
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState)
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState)
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.<WaitForQuiescence>g__ProcessAsynchronousWork|54_0()
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.WaitForQuiescence()
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.RenderRootComponentAsync(Int32 componentId, ParameterView initialParameters)
   at Microsoft.AspNetCore.Components.Endpoints.EndpointHtmlRenderer.WaitForResultReady(Boolean waitForQuiescence, PrerenderedComponentHtmlContent result)
   at Microsoft.AspNetCore.Components.Endpoints.EndpointHtmlRenderer.PrerenderComponentAsync(HttpContext httpContext, Type componentType, IComponentRenderMode prerenderMode, ParameterView parameters, Boolean waitForQuiescence)
   at Microsoft.AspNetCore.Mvc.TagHelpers.ComponentTagHelper.ProcessAsync(TagHelperContext context, TagHelperOutput output)
   at Microsoft.AspNetCore.Razor.Runtime.TagHelpers.TagHelperRunner.<RunAsync>g__Awaited|0_0(Task task, TagHelperExecutionContext executionContext, Int32 i, Int32 count)
   at Oqtane.Pages.Pages__Host.<ExecuteAsync>b__14_1() in C:\Source\Projects\oqtane.framework\Oqtane.Server\Pages\_Host.cshtml:line 29
   at Microsoft.AspNetCore.Razor.Runtime.TagHelpers.TagHelperExecutionContext.SetOutputContentAsync()
   at Oqtane.Pages.Pages__Host.ExecuteAsync() in C:\Source\Projects\oqtane.framework\Oqtane.Server\Pages\_Host.cshtml:line 6
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageCoreAsync(IRazorPage page, ViewContext context)
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageAsync(IRazorPage page, ViewContext context, Boolean invokeViewStarts)
   at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderAsync(ViewContext context)
   at Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, String contentType, Nullable`1 statusCode)
   at Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, String contentType, Nullable`1 statusCode)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeResultFilters>g__Awaited|28_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|7_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Oqtane.Infrastructure.JwtMiddleware.Invoke(HttpContext context) in C:\Source\Projects\oqtane.framework\Oqtane.Server\Infrastructure\Middleware\JwtMiddleware.cs:line 90
   at Oqtane.Infrastructure.TenantMiddleware.Invoke(HttpContext context) in C:\Source\Projects\oqtane.framework\Oqtane.Server\Infrastructure\Middleware\TenantMiddleware.cs:line 83
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Server.IIS.Core.IISHttpContextOfT`1.ProcessRequestAsync()

Hope something here helps as I am trying to figure out ways to troubleshoot my IDE and seems at least I found a way to move forward, dont use SSL except it's ok to use it via SQL configurations I believe. Just not for browsing.

And that is testing in 2 environments recent updates.

I spun up a Windows 2022 server just to make sure it wasn't just my environment.

My solution was I can only access Oqtane currently via HTTP and cannot using HTTPS. Maybe a new issue.

After testing I could load the HTTP site once I removed the HTTPS binding.

However I also got an error at the bottom of the page saying the site needs to reload which was different no matter which version of SQL I used localdb or SQL Server.

Out for the night hope this helps.

@thabaum
Copy link
Contributor

thabaum commented Feb 28, 2024

Anyone know if this issue still exists?

I am going to review this issue see if it exists in 5.1.0 along with any other installation issues with some different environments over the next week to run some tests starting with Windows Oqtane installs. I believe I have been able to install Oqtane on most every database since 5.02.

Usually it's been a config issue such as the database name or something not properly setup for the environment working on such as an issue with the configuration of the database server itself allowing the connection. I will be sure to try to explain the options best I can to help clear up any issues like this if can be. In fact I believe we have a docs meeting tomorrow I will bring this up as we get things prepped for an upcoming push.

Cheers!

@DiegelB
Copy link

DiegelB commented Aug 24, 2024

An issue that I was running into and it worth checking out, is make sure that you allow SQL logins and not just windows authenticated logins in your SQL database.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

5 participants