January 31, 2023 at 6:19 am
I am trying to migrate a DB from an old dying laptop to a new one, both HPs. The old one had SQL 2017 on it the new one SQL Express 2019.
I have created the logins and linked them to the users that were orphaned in the backup DB/Restore process as it was quicker with an external drive to copy and set up than trying to do loads of inserts/creates etc.
I have a VS C# project that is trying to login to the DB. The test program is a command run .EXE that hooks into a DLL where the connection string is in a GetSettings.cs class.
The old one worked fine with the SQL Connection login (read/exec on CUD procs), I would login to the DB SQL management console with my OLD Windows login > (LocalDB)\MSSQLLocalDB (user blanked out HP\Reid) and the app would use the SQL connection string > SERVER=(LocalDB)\MSSQLLocalDB;DATABASE=brainiac;uid=brainiac;pwd=bxxxxx;
On the new computer, I am getting loads of errors when trying to run the app. I can login to the DB in SQL Management Studio with my Windows details > ROB2022\SQLEXPRESS (user blanked out ROB2022\rober) in properties under connection it says ROB2022\SQLEXPRESS (ROB2022\rober)
Running SELECT @@SERVERNAME I get ROB2022\SQLEXPRESS
Running SELECT select user_name() I get dbo
I did somewhere for a second see, and log, HP\LOCALDB#9190A7A7 I guess that is the instance of the installation but when I tried it in the connection string
SERVER=HP\LOCALDB#9190A7A7;DATABASE=brainiac;uid=ROB2022/Rober;
I got this error:
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified);
I tried my SQL login that I recreated, relinked and chose the exact same settings as the old DB, schema DBO, Default DB > Brainiac and only Read/Exec permissions.
But when I try all these in the connection string they fail with these errors:
SERVER=ROB2022\SQLEXPRESS (ROB2022\rober);DATABASE=brainiac;uid=brainiac;pwd=bxxxxx;
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified);
SERVER=ROB2022\SQLEXPRESS;DATABASE=brainiac;uid=brainiac;pwd=bxxxxx;
Login failed for user 'brainiac'
It's the nearest I can get as it's got past the server but the login is not working, I have mapped it in to my DB as a dbdatareader / public and default DB Brainiac / dbo, but it just doesn't work. I don't know if there is something I need to tick/select in 2019 that wasn't in 2017 but it is exactly the same password, copied and pasted AND typed.
Under securables it's a taf different from 2017 but the only thing selected for my SERVER is Connect SQL - Grantor sa, Grant ticked. This is by default and cannot be changed.
It's not a spelling mistake in the password as I've tried with a simple and even one-letter password. I don't know if the ADO parameter names have changed from 2017 to 2022 but I've tried changing the connection string to:
SERVER=ROB2022\SQLEXPRESS;DATABASE=brainiac;USER=brainiac;PASSWORD=b;
SERVER=ROB2022\SQLEXPRESS;DATABASE=brainiac;USER=brainiac;PWD=b;
SERVER=ROB2022\SQLEXPRESS;DATABASE=brainiac;UID=brainiac;PWD=b;
With no effect.
It has to be something to do with the login/user as I am getting into the server just not logging into the DB.
I have copied everything from my old system and even deleted all the users and logins and recreated them, although the re-linking of orphaned users with a script I made using EXEC sp_change_users_login 'UPDATE_ONE' worked to relink logins and users as they had the same name. No outside params are passed in so it's a closed system, a service I hope to run on my new laptop but I just can't get past what should be a simple DB setup and app login connection string.
I just cannot get around this Login failed for user 'brainiac' error message. I first copied the backed up DB which I have no problem getting into by windows auth in SQL Management Studio. Re-linked orphaned users at first to connect to the same named logons, then deleted all the DB users and recreated them from server logons, played about with server roles, permissions which should be read only (and EXEC on stored procs/UDFs), put 1 symbol passwords in to rule out my Morphine heavy eyed typing mistakes as I thought this would be a nice easy DB task for mew tonight moving from my old HP which is failing to my fast new HP with just newer versions of VS and SQL Server Express.
I have never had this problem before with such DB moves so I can only assume it is something to do with the newer software and some sort of hidden setting or rule I need to check first that in previous versions I didn't. It has to be something with the login->user setup somewhere but I have no scooby wtf it could be!
I did want to keep my Windows logon for MS Studio, and use SQL Logins for the app connection but if I need to resort to a Windows auth connection string to get into the DB, seeing there is no outside interference which might enable injection hacks etc then I guess I will have to. I just never have had to do that before, so I am not sure of the connection string I would need to use in the DLL.
So I am off to bed before the doctor for more cancer treatment and I will hope to find some answers from all you very helpful people when I get back maybe??
Thanks in advance for any help given, and gladly received.
January 31, 2023 at 10:32 am
This was removed by the editor as SPAM
January 31, 2023 at 11:39 am
Login failed for user 'brainiac' is the key.
EXECUTE xp_readerrorlog
and find out the specific reason why the login failed. it will tell you exactly the problem on the next line AFTER the string Login Failed....it is probably something simple like password must be reset on first login, or password does not match, user locked out or disabled...it's in the log, but you have to dig for the specific reason.
Lowell
January 31, 2023 at 3:19 pm
Hi.
Thanks for that. At first I thought it was that the SID from the Master DB was different from the DB I had restored which it was, and I fixed it with a:
--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()
--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'
-- fix it changing it to my domain
ALTER AUTHORIZATION ON Database::brainiac TO [Rob2022\rober]
However when I ran the code it still errored so I looked for the last error and it said this
Login failed for user 'brainiac'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: <local machine>]
I have never had that error before, I don't know if it's easy to change the server setting so that I can login to the SQL Management Console with a windows login like on my old machine and still use an SQL login for the closed app I am running or not.
If not what should be my connection string params in my service to login using Integrated Authentication. I have never used that before, and I'm not sure if it's wise or not. Even though no external params are entered into the system that could cause injections I would prefer to use a separate SQL Login for my service and not my domain name just for security reasons.
Thanks for the help though. I have only had 30 mins to look at this and now I have to go back to hospital otherwise I would Google the Shiz out of it as I hate bugs, and stayed up a long time last night trying to fix this.
January 31, 2023 at 3:42 pm
Server is configured for Integrated authentication only
So you have it set to use WINDOWS only not WINDOWS and SQL.
Easy to solve,
Right Click on the server name in SSMS and do properties
Click on Security
Switch the radio button to "SQL Server and Windows Authentication mode"
Open services.msc
Find the SQL Express service and restart it.
And you should then be able to connect.
January 31, 2023 at 7:38 pm
Unless I am missing something - you are not going to be successful using SQL Server Express Edition. It looks like the original application was using SQL Server Express LocalDB.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 31, 2023 at 11:31 pm
Hi,
Thanks, Ant-Green, that fixed it for me, just had to sort out a load of CLR Assemblies and re-create the DLL's that were copied onto my old laptop from work to do RegEx functions, and had to get my head around the new security options, although as it's a closed service that is now running perfectly on my new HP laptop no-one can inject into it so a ALTER DATABASE BRAINIAC SET TRUSTWORTHY ON as the only assemblies are the ones I just had to re-create from a new VS project and install.
As for Jeffery's question about the name/type of the server, I remember having big issues with trying to find out the actual Server Info when I installed SQL Server Express 2017 on my old machine. I remember there were different values for @@SERVERNAME and what was showing in the connection boxes and instances and all sorts, I cannot remember how I got it sorted but I did and last night I copied it into an SQL 2019 Server DB (SQL Express), and after fixing all the loose ends it is now working.
I just need to fix the issue of no MS SQL Agent, and I guess I will have to rely on timers in my service to run the maintenance, and clean up jobs to keep tables small, unless anyone has a better idea? I never got round to it in the last version after the keys T and Y stopped working, and in SQL that is a lot of copy and paste for SELECT and ORDER BY statements so I waited until moving to this box before handling that.
Thanks for the help though!
Rob
February 1, 2023 at 1:26 am
Hi,
One thing that is puzzling me, is that when I run the BOT on my new "faster", "newer", "HP Intel" laptop as opposed to my old one, is that stored procedures called from the BOT are taking 5 minutes to run. However, if I copy the code out and run it in SQL Management Studio it runs pretty fast, a few seconds for what is quite complicated logic and various conversions etc.
I am still running the BOT on my old HP and it is faster than the new one, I don't know why, I rebuilt all indexes despite not really needing to due to the BACKUP / RESTORE which copied the most up to date ones anyway.
Then I ran EXECUTE xp_readerrorlog again and I see my log file is full up of these records which seem to indicate some kind of memory issue looking at the speed of the processes spawned, marked as an issue then unloaded. I am guessing this is the BOT not running SQL in MS due to how many records and the little time gap between them.
LogDate ProcessInfo Text
2023-02-01 00:48:01.930 spid63 AppDomain 77 (Brainiac.dbo[runtime].76) created.
2023-02-01 00:48:08.490 spid36s AppDomain 77 (Brainiac.dbo[runtime].76) is marked for unload due to memory pressure.
2023-02-01 00:48:08.490 spid36s AppDomain 77 (Brainiac.dbo[runtime].76) unloaded.
2023-02-01 00:52:37.300 spid63 AppDomain 78 (Brainiac.dbo[runtime].77) created.
2023-02-01 00:52:45.620 spid32s AppDomain 78 (Brainiac.dbo[runtime].77) is marked for unload due to memory pressure.
2023-02-01 00:52:45.620 spid32s AppDomain 78 (Brainiac.dbo[runtime].77) unloaded.
In Resource Monitor Memory it's running at 85-90% the top two processes being.
Microsoft Visual Studio with about 15 child processes under the tree so obv the BOT running and,
SQL Server Windows NT (64) with SQL Server (SQLExpress) as the only child.
So I guess there is some issue somewhere I am getting on a laptop a good 6 years newer, running Windows 11, whereas my other old HP uses an AMD A8 processor running Win 8.1r, It does have half the RAM than the older laptop 4GB to 8GB, but I thought the VM disk swapping and newer chipset 11th Gen Intel(R) Core(TM) i5-1155G7 @ 2.50GHz 2.50 GHz, would make up for it.
Maybe not.... maybe I should just get the T & Y keys fixed on the old laptop, and use this very quick to run virus checks, disk scans/checks, Browsing, Start up in a couple of seconds, newer laptop just for browsing, however, I saw how fast it was doing everything else so I thought the BOT would run faster on it - plus I can type T&Y, the copy, and pasting was killing me.
Thanks for any help given and to be received by the way...
February 1, 2023 at 12:17 pm
This was removed by the editor as SPAM
February 1, 2023 at 12:20 pm
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply