I built my first app 2 years ago in Bonfire, using PHP and MySQL… now I’m trying to update it in ASP.NET Core and it seems like the first step to start scaffolding is to migrate to MsSQL server, it looks like I should be running the local express that comes with Visual Studio, but I don’t seem to have an access point to start the server or even see if it is running… How do I access this SQL Server Agent?
I’ve been looking at MySQL work bench to see what I might spot, so far the closest I’ve gotten is connecting with a native ODBC, just because it assumed the server name for me, as localhost\SQLEXPRESS… but now it’s asking for a password, that I never had an option to set, I’ve tried leaving it blank.
If connecting to ODBC does the job for you, try setting a password for the sa user.
There are couple of steps you have follow.
###Enable mixed authentication
If you know you can login through SQL Server Authentication, you can skip this step. This enables both Windows login and Forms login.
- Login to your local instance SQLEXPRESS on SQL Server Management Studio (SSMS) using Windows Authentication.
- RIght click on your instance on Object Explorer on the left. Go to Properties.
- Go to the Security tab.
- On the Server Authentication, Select SQL Server and Windows Authentication Mode.
- Save and restart.
###Enable sa user and set/update the password - Login to your local instance SQLEXPRESS using Windows Authentication again.
- On the Object Explorer window, expand the instance. You’ll see a bunch of folders, Databases, Security, Server Objects and so on.
- Expand Security → Logins.
- You’ll see the user sa. Right click and go to Properties.
- On the General tab, you’ll see the password, change it to whatever you like,
- Now, go to the Status tab and set Enable to Login.
- Likewise, set Permission to Grant.
- Save and restart.
Now, try using the username and password for ODBC connect.
I think I was stuck on the acronyms… that I should choose between SSMS or SSMA. Thinking that one was a modern version to replace the other, but Management Studio is the interface I’ve been missing and then Migration Assistant allows you to specialize, like an external extension… Thank you.
I’ve started a new thread, because the nature of my question has changed slightly… I have tried starting at the beginning just going through the documentation on SSMS, but it still wants to say I don’t have an SQL. Starting an SQL Server
I’m getting really close… I can smell the data. It looks like I can see both the MySQL and the SQL servers, but it keeps telling me “You must start SQL Server Agent to use Server Side Migration”, which is what I think I’m trying to do, so I feel like I’m still missing a start button somewhere…
To start the SQL server agent:
- Go to SQL Server Configuration Manager (a quick search on Windows will get you there).
- Expand the Services and start the SQL server agent. You might have to do this for each instance.
so when I do my ‘cortana’ search I don’t get anything local… but following the top link gives me the page on the right which leads me to
I’ve followed down as far as adding a new user or group… but I’m unsure as to which of the four I should add it too… I’m thinking administrators or Authenticated Users… I just want to run it as my local self with out having to pick run as administrator every-time unless I really should… so far, on this current set up, I haven’t had to run anything as an adminastator, but maybe doing so somewhere else earlier in the process would have made a difference…
That’s not it. I just did a quick search on my computer, I couldn’t find it either. It seems the newer versions of Windows don’t show it as an application.
In the Cortana search, type SQLServerManager13.msc (since your SQL server seems to be 2016).
If you still don’t find, just change the number to 12, then 11 and so on.
If you still don’t find it, look at the SQLServerManager(version).msc at C:\Windows\SysWOW64\SQLServerManager(version).msc
If you do find it, there are also a couple of things you might want to enable in the configuration manager.
- Expand SQL server network configuration, click on Protocols for MSSQLSERVER, Enable Named Pipes and TCP/IP. Do this for all the instances you have.
- Now click on SQL Server Services, right click on the SQL Server Browser, go to Properties. Then go to Service tab, set Start mode to Automatic. Save it. And right click again to start it. Do this for SQL Server Agent as well.
- Right click SQL Server (MSSQLSERVER) and any other you have, and then Restart
I realize it’s not it directly… I found the SQLServerManager13.msc but It’s giving me this error about WMI provider… I was trying to get past that, it still won’t let me open it even after finding it…
Take a look at this:
Try running the command as Administrator in your command prompt.
mofcomp "%programfiles(x86)%\Microsoft SQL Server\130\Shared\sqlmgmproviderxpsp2up.mof"
Number 130 may not be the correct number, check the path in your explorer.
After you get the Done!, restart the WMI service (Windows Management Instrumentation)
For that:
- Run services.msc
- Find Windows Management Instrumentation and restart it.
That got me into the Configuration Manager, but it’s still not showing anything under the SQL Server Network Configuration… I did notice on that last link that it mentioned a know issue with 64 bit, which is what I’ve been picking in my whole setup and this still says 32… should I try going through it again picking 32 every time?
Strange. I found these links:
- https://blogs.msdn.microsoft.com/sqlserverfaq/2010/03/16/sql-server-2008-configuration-manager-on-a-64-bit-machine-does-not-display-network-configuration-native-client-configuration-options/
- http://dba.stackexchange.com/questions/108899/sql-server-services-empty-in-sql-server-configuration
Try the first one as it seems easy. If not, reinstalling windows might help.
Keep me updated.
Maybe it’s time to try Azure… I’ll host the MySQL locally and let them run msSQL in the cloud, then connect and do my conversion there…
It’s probably something in the MySQL installation overwriting it, I’ll try just doing the MsSQL this next round.
Yes, that sounds like a good idea. Let me know if it worked for you.
Following the first link, I found a copy in the (x86) directory… but it says that’s the 32 bit version, I don’t have a copy sitting around on another machine of the 64 bit version, as it suggest… should I try just copying it over?
No, the link says to replace the 32bit file with another 32 bit file, and 64 bit file with another 64 bit. I don’t think copying this file to the 64 bit version would work.
Can you try doing this:
- In the Sql Server Configuration Manager, go to SQL Server Services, do you find the SQL Server Agent there?
- If you do, start the agent.
- To enable the remote connections, try this link: http://stackoverflow.com/questions/16984719/sql-server-network-configuration-protocols-not-available. It creates a new login and sets the remote configuration using the sql commands.
Note: Change login-name and password to your liking
Let me know if you can connect from Migration Assistant now.
I may just have the home addition of Windows 10, could that be the difference? Do I need to be running Window’s Server…