SqlServer2016Expessで構築してあるサーバーを完全に別のPCに移設して使おうと作業していたら色んな問題が出てきて困ったので備忘録に残しておこうと思います。
今回の作業内容は、SqlServer2019Expessを別のPC（Windows10）にインストールして、データベースを復元させて外部のAccessから接続できればOKという内容でしたが、すんなりとは行きませんでした。
Sqlserverの設定を過去の記事参考
過去のSqlServer初期設定の記事を見て設定を進めていきました。
https://pctips.jp/server/sqlserverexress20180507/
全部の設定が終わり追加したユーザーでODBC接続をしようとしてもエラーが出て接続できませんでした。
SqlServerにユーザー接続できなかった理由
ローカルサーバーから、Windows認証でのログインは当然できていましたが、追加したユーザーでのログインができませんでした。
まず最初に設定し忘れていたのが、サーバー認証の変更です。
ココに注意
SQL Server認証モードとWindows認証モード
という混合モードにしておかなければなりませんでした。
それでもログインできなかったので、パニクッテ無駄な時間を費やしましたが、
注意ポイント
コンピューター名を変更した事の設定
データベースとログインユーザー名の紐付け
が必要だったようです。
MSSMSから「新しクエリ」にて
Use [移設したデータベース名]
ALTER USER 「データベースのユーザー名」 WITH LOGIN = 「ログインユーザー名」
自分の環境では、データベースのユーザー名とログインユーザー名は同じでした。
やっとこれでログインすることができました。
ODBC接続エラー
今までうまく行っていたODBCは、
ODBC Driver 13 for SQL Server
でしたが、なぜか？接続できません。SqlServerでやると接続はできますが、ちょっと挙動が変です。
接続しているAccessの方で、ODBC接続がきれるようです。
そして、うまくいった接続方法が、
SQL Server Native Client 11.0
になりました。
良く調べれば当然の事なのかもしれませんが、SqlServerExpressのバージョンを１６から１９にあげたので、ODBCも変更しないといけないのかもしれません。
SqlServerバージョンの違い！？
SqlServerの設定が終わったマシンを別環境にもっていき、接続しようとするとODBC接続ができません。
更に、別環境のパソコンには、
ココに注意
SQL Server Native Client 11.0が存在しない
のです。
ダウンロードしてインストールしようとしましたが、マイクロソフト以外の変なサイトに誘導されるので止めました。
そもそも、Native Clientってのは何がちがうのだろうか？色々探し回って下記の様な回答にたどり着きました。
参考
SQL ServerドライバーのフルネームはSQL Server ODBC Driverです。SQL2000以降の古いドライバーです。これを使用してSQL Server 2016に接続できますが、新しいサーバーにアクセスすることはできません。
SQL Server Native Clientには、SQL OLE DBプロバイダーとSQL ODBCドライバーの両方が含まれ、SQL Serverへのネイティブ接続をサポートし、SQL Server 2016のすべての機能をサポートします。
ちょっと情報が古いのですが、まとめると、
はてな
SQL Server Native ClientじゃないとSQL Server２０１７以降は接続できない？？？
ってことでしょうかね。
先に回答しておきますが、SQL Serverドライバーで２０１９でも接続可能でした。
今回の問題での解決方法
ODBCとは関係ありませんが、
ココがポイント
SQL Server2016のデータベースをSQL Server2019で復元すると、ログインユーザーの紐付けが必要
ODBC接続ができないので、何度もODBCの接続設定をしましたが、結局は、MSSMSで、下記のクエリを実行して、ログインユーザーの紐付をしないと接続できませんでした。
Use [移設したデータベース名]
ALTER USER 「データベースのユーザー名」 WITH LOGIN = 「ログインユーザー名」
※2019でバックアップしたデータは、2019で復元した場合は上記の作業は必要ありませんでした。
※バージョンの違いではなく、サーバーの違いからでる作業の可能性も考えられます。
MSSMSでデータベースを復元しようとしてもエラーになり復元できない現象への対応は、
ココがポイント
MSSMSでSQL Serverを再起動すれば、復元可能
になりました。
ODBC接続は、SqlServerのバージョンに関係なく、SQL Serverドライバで接続可能になりました。
別に、
ココがポイント
SQL Server Native Clientじゃなくても接続可能
のようです。
本当にマイクロソフト様には、無料でSQL Server2019Expressを使わせていただいている以上、文句を言うような権利はございませんが、バージョンが変わると色々と不具合が出ることは何とも悲しい時間の経過があります。