[Bug]: Can't install Dnn10 on SQL Server 2017

[valadas]

Is there an existing issue for this?

• [X] I have searched the existing issues

What happened?

I recently needed to install DNN10 to test ongoing work. I could not install it on SQL Server 2017, it did work on 2019 though

Steps to reproduce?

1. Get the current DNN10 install zip
2. Make sure to use a database server lower than 2019
3. Install as usual
4. Notice error during install process

Current Behavior

Does not install

Expected Behavior

Should install

Relevant log output

0:02 Minutes   |   16% ERROR occured - System.Data.SqlClient.SqlException (0x80131904): 'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE TABLE option. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDatabaseConnectionProvider.ExecuteNonQuery(String connectionString, CommandType commandType, Int32 commandTimeout, String query) at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script, Int32 timeoutSec) ClientConnectionId:4e2f2ab9-a279-420a-ac1e-1b685103c4f9 Error Number:155,State:1,Class:15 CREATE TABLE dbo.ApiTokens ( [ApiTokenId] [int] IDENTITY(1,1) NOT NULL, [TokenName] [nvarchar](100) NULL, [TokenHash] [varchar](96) NOT NULL, [ExpiresOn] [datetime] NOT NULL, [Scope] [int] NOT NULL, [PortalId] [int] NOT NULL, [IsRevoked] [bit] NOT NULL DEFAULT (0), [IsDeleted] [bit] NOT NULL DEFAULT (0), [RevokedByUserId] [int] NULL, [RevokedOnDate] [datetime] NULL, [CreatedByUserId] [int] NOT NULL, [CreatedOnDate] [datetime] NOT NULL, [LastUsedOnDate] [datetime] NULL, CONSTRAINT [PK_ApiTokens] PRIMARY KEY CLUSTERED ( [ApiTokenId] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] System.Data.SqlClient.SqlException (0x80131904): 'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE TABLE option. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDatabaseConnectionProvider.ExecuteNonQuery(String connectionString, CommandType commandType, Int32 commandTimeout, String query) at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script, Int32 timeoutSec) ClientConnectionId:4e2f2ab9-a279-420a-ac1e-1b685103c4f9 Error Number:155,State:1,Class:15 CREATE TABLE dbo.ApiTokenKeys ( [ApiTokenId] [int] NOT NULL, [TokenKey] [nvarchar](200) NOT NULL, CONSTRAINT [PK_ApiTokenKeys] PRIMARY KEY CLUSTERED ( [ApiTokenId] ASC, [TokenKey] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] System.Data.SqlClient.SqlException (0x80131904): Cannot find the object "dbo.ApiTokenKeys" because it does not exist or you do not have permissions. Adding foreign keys to dbo.[ApiTokenKeys] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDatabaseConnectionProvider.ExecuteNonQuery(String connectionString, CommandType commandType, Int32 commandTimeout, String query) at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script, Int32 timeoutSec) ClientConnectionId:4e2f2ab9-a279-420a-ac1e-1b685103c4f9 Error Number:4902,State:1,Class:16 PRINT N'Adding foreign keys to dbo.[ApiTokenKeys]' ALTER TABLE dbo.ApiTokenKeys ADD CONSTRAINT [FK_ApiTokenKeys_ApiTokens] FOREIGN KEY ([ApiTokenId]) REFERENCES dbo.ApiTokens ([ApiTokenId]) ON DELETE CASCADE

Anything else?

Looks like that was added in #5736

Affected Versions

10.0.0 (alpha)

What browsers are you seeing the problem on?

Chrome

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[sleupold]

I agree, we should avoid using options, when creating SQL Server objects, if possible, Note for core developers: To avoid compatibility issues, please set compatibility level of the database to the min supported SQL Sever version by DNN Platform.