Connect Workstation
By Richard Rost

14 days ago

Lesson 8: Connect to SQL Server from Another PC

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this lesson, we will walk through the steps to connect to SQL Server from another machine on your network. I will show you how to configure SQL Server Express for remote connections, enable the necessary protocols and services, check your Windows Network Profile, and set up both Windows and SQL Server Authentication logins. We also discuss how to open the correct firewall ports and troubleshoot common issues if you are unable to connect. By the end, you should be able to access your SQL Server remotely from a workstation.

Navigation

Prev: Lesson 07
Next: Lesson 09

Keywords

SQL Server for Access, connect to SQL Server from another machine, SQL Server Express remote network setup, enable TCP IP protocol, SQL Server Configuration Manager, SQL Server Browser Service, mixed mode authentication, SQL Server authentication, Windows

Comments for Connect Workstation

Age	Subject	From
14 days	Connecting Access Front End to SQL Server	Paul Whiteway

Subscribe to Connect Workstation
Get notifications when this page is updated

Intro

Transcript

In lesson eight, we're going to connect to SQL Server from another machine on the network. This is something a lot of you have been asking for. Up till now, everything we've done has been on the server itself. But in the real world, your users and you are going to need to access it remotely across your network.

So we're going to walk through the basic setup to get connected, plus what to check if it doesn't work right away. And a lot of times, it's not going to work right away, so we're going to cover that in this video.

Now, I am going to say off the bat that I'm going to show you the basic setup first. Then, after that, we're going to go through some things to check if it's not working. That's one of the reasons this lesson is so long - there are a bunch of troubleshooting steps that I'm going to walk through. But as soon as you get connected to your SQL Server from your workstation, you're good. You can stop watching the video at that point unless you want to see all these different troubleshooting steps, then stick with me. Don't be discouraged because this video is about 30 minutes long. You might not need the whole thing. We're going to get connected first, and then we'll go through some troubleshooting steps for people who have problems.

Originally, I was going to do queries next, but looking at all the feedback in the forums and in the YouTube comments, you really want to see how to connect to this thing from another machine. I get it. That's what you're going to be doing most of the time. Actually, you're not working on the server itself. The server sits over in the corner and you connect to it from a different machine to do your management, and your end users will connect from their machines. So we're going to spend this lesson on doing just that.

At this point, everything we've done has been on the server itself. We created our database, built tables, and entered data. In the real world, users aren't sitting at the server, so let's talk about how to get connected to the server.

Now, most of you, I'm assuming, have been running a split Microsoft Access back-end database. If it's on this computer, then you're halfway there. If this is your machine that hosts your back-end table database, then you're in good shape. You've proven the machines can authenticate, browse shared folders, and communicate with each other.

If you haven't done that yet, you should. In fact, just a couple of days ago, I recorded this video because I knew it would be coming up. This shows you how to share a folder on your network and set up two machines so they can talk to each other. You need to set up a folder on your server machine that your workstation machine can connect to if you're sharing Microsoft Access databases. If you can do this, then you're 90 percent of the way there to making sure you can connect to your SQL Server. You don't have to do this, but I recommend it to at least make sure that everything is working between your machines.

If you go through these lessons and you can't connect to your SQL Server, try this stuff first. This is more in-depth about what happens on the Windows end. Go watch this. In this video, I show you how to set up shared folders, set up user accounts, all that stuff. And again, you don't need file sharing for SQL Server to work, but if your machines can open shared folders and see each other, that tells you your network permissions and authentication are already functioning properly. Think of that like the connectivity test before we start working with SQL Server.

Now, here's the thing with SQL Server Express: remote connectivity is locked down for security out of the box. When you install it, SQL Server Express is configured to accept only local connections. I know a lot of you were posting, "Hey, I can't connect to this thing." Well, yeah, I'm going to show you why.

First off, of course, make sure you can connect to SQL Server Express with SSMS. The SQL Server service is actually running. I know this sounds obvious, but it's one of the most common things people miss.

Next, we're going to open up the Configuration Manager for SQL Server and make sure that the TCP/IP protocol is enabled. How do we do that? Click on Start and then search for "config" and you should find the SQL Server 2025 or whatever version you have, Configuration Manager. Open that up. Say yes. And here it is right here. Again, pin it to your taskbar; you're going to use this quite a lot until you get everything up and running, especially on your server. Just leave it pinned.

Here's your SQL Server SQL Express - it's running. Make sure it's running. We're going to expand the SQL Server Network Configuration, not this 32-bit one, the one down here. Click on Protocols for SQL Express and right there you see TCP/IP. It says "disabled." That's how it ships. That's why you can't connect to it. It's a security feature. It's not a bug; it's a feature. We're going to right click on that and go to "enable." Change it to enabled. However, they won't take effect until the service is stopped and restarted. We'll do that in a second.

Without TCP/IP, SQL Server only listens locally to connections coming from the machine you're on. It won't accept any connections from over the network.

The next thing we're going to do is turn on the Browser Service. Click right here where it says SQL Server Services. See this thing? It says SQL Server Browser and it says "Stopped." This is the thing that lets you browse to see the server. When you say, "Hey, I'm looking for servers out there," it actually responds. In high security environments, you want to leave this disabled so people can't just browse and see what services you have out there. For most small business installations, you want to be able to browse and see the server.

We're going to right click on that and go to Properties. In here, go up to the Service and then Start Mode. We're going to make this "Automatic," so it automatically starts up when SQL Server starts. Hit OK. Then we're going to start it. Right click and "Start." That should start it up.

We have to restart the server anyway. So right click on this guy and go Restart. That will restart the SQL Server service so it takes our TCP/IP change. Once you do this, you shouldn't have to do it again unless you install another SQL Server. Then it's going to start back up, and now we're back and running.

A lot of you are probably wondering what this SQL Server Agent is. We're going to get to this eventually in the more advanced lessons. This is for things like scheduled backups, data imports and exports that happen automatically, index rebuilds, alerts and notifications, nightly jobs, scripts, that kind of stuff. We're not going to install that now. Get it working now. This is a more advanced feature. We'll get there eventually.

We're all done in the Config Manager for now. Close that.

One more thing I want to check is to make sure that your Windows Network Profile is set up correctly and you're on a private network and not a public network. I talk about this in that file sharing video, but just in case you didn't watch it, we're going to open up Settings. It's under Start, Settings, or if you listened to me, you pinned it to your taskbar. It's under Network and Internet. Make sure your properties say "Private Network." Remember, private network is your home, your office; public network is working at Starbucks. You don't want that. You want your Ethernet connection, because you're wired, to be a private network.

If not, go watch my file sharing video. I talk more about how to set that up.

Now, I've got one more little adjustment I'm going to make to what I told you earlier in the class. I said we're going to be using just Windows Authentication throughout this course, but we're going to also add SQL Server Authentication as well. If you're in a small office running a peer-to-peer network and you've already been sharing a split Access database, Windows Authentication will work just fine for you. If your machines can already browse each other, open shared folders, connect to a shared back-end file, then your network permissions are already in place; you're good to go. All you really need are matching Windows user accounts on the SQL Server machine so the server recognizes who's connecting. I showed you how to do that in my file sharing video.

Sorry if my mouse pointer is bouncing around. I'm still using a remote connection here and every time I go to pause it, I have to move off the screen, which stops on the last little thing that I held it over.

But I recognize that everybody who's watching this course is coming from a different environment. Some of you are on domains, some are on workgroups, some are using local Windows accounts, some are using Microsoft logons. I don't want to turn this into a Windows Networking class, but here's how we're going to handle it.

If your peer-to-peer network is already working, you can follow along using Windows Authentication. But if you don't already have that environment in place and you want the simplest and most consistent method, I'm also going to show you how to use SQL Server Authentication. SQL Server Authentication uses usernames and passwords that are stored inside SQL Server itself. It doesn't rely on Windows accounts and it works the same regardless of your network setup. In fact, if you connect to an SQL Server over the internet, chances are you're using SQL Server Authentication. If you set up an SQL Server with someone like Winhost or whoever your web provider is, that's what you're using.

Now before we can create SQL logins, we need to enable mixed mode authentication. So on the server machine, open up SSMS, this guy, right click your server name, go to Properties and then go to the Security tab right there, tab or item or whatever it is. Over here you're going to see "Server authentication." The default is "Windows Authentication mode." We're going to change it to this guy, "SQL Server and Windows Authentication mode." If you change the setting, you're going to have to restart SQL Server. We know how to do that. I'm going to close this. We're going to go back into the settings. Yes. We're going to restart SQL Server - right click, restart and wait for a minute while it does its thing and it starts back up. There we go. We're going to close this again now.

Back into SSMS. Now watch this. Here's something new. This is here because it was a recent connection. Click on Browse, click on Network, give it a second. There it is. Look at that. You should be able to see it. Now it's browsing through the network and shows that server. You might be able to even see it from other machines right now. Go give it a shot if you want to. But I'm going to go back to history. We're going to connect back to this guy using Windows Authentication. That's fine. You have to be in here in order to set up user accounts.

Now over here, let's go to Security. Go to Logins. Now here's a whole bunch of built-in logins. Then I may have made out or set up some previous ones. Ignore those. Right click on Logins and go "New Login." This guy comes up. Type in a login name. I already have one, Richard, that I set up earlier. I'll just make another one. We'll call this one Rick. This will be SQL Authentication. Give it a password. I'll just do 123123, whatever. This stuff's up to you.

If it's a small office, I usually turn this stuff off - making them change their password at next login - just no. If you have a bigger office and you care about stuff like that, then that's completely up to you. I'm going to cover a lot of this stuff in more detail in future lessons. Again, today my goal is just to get you up and connected.

Now let's say this is your account and you want to give yourself permission to make changes to the data, work with the tables, change the design. Go to User Mapping, pick the database you want to give control over. That's this one here. That's what we're working with. That's our SQL for Access Users database. And down here you're going to check "db_owner." That gives you full control, including design. There's a whole bunch of other stuff in here too, but we're not going to go over all of it. Hit OK. Now you just created your account. You've got full design control over the database.

Want to set up another one for your users? You want to be able to let them work with the data like they did in Access, but not change the design of the tables. They can read new records and stuff. Right click, New Login. Let's just call it "users." You can make one "users" account for everybody. Later on, I'll show you how to make individual accounts and you can say, okay, this user has access to these tables, this one does not, and so on like that. But for now, we'll keep it simple: SQL Authentication. Again, put a password in. 123123 is fine for now. Turn this stuff off. Go to User Mapping.

Now for these people, you're going to give them data reader. Oh, you have to pick the database first, my bad. I do that all the time. Pick your database. Data reader and data writer. That's all they need. They can read data and they can write data. They can read all the tables, they can insert records, update records, delete records. That's basically full data interaction, just like an Access user editing tables via forms or whatever else you have in your Access database. They don't need anything else in here.

And again, if you want to have just a read-only user, you can do that too - just someone, like if you're putting a kiosk out front with some sales data in it or some stuff that you want people to see. This is one of the major strengths of SQL Server: data control like this. You don't have this kind of flexibility with Access. Hit OK. Now we have a users account. Pretty straightforward.

Now here comes the fun part. I'm going to close SSMS. And I'm going to go to my actual workstation now because I've been remote desktoping into my server. Here's SSMS on my regular workstation. Normally, the window pops up, but I closed it. So we're going to go File, Connect. Here's my website, that's my IP address and all that. No, you can't have my password. You're going to hit Browse and go to Network from my workstation and see what shows up.

Now, nothing showed up after it did its little browse thing. If that happens, don't panic. The browse list in SSMS is notoriously unreliable, especially on home and small business networks. SQL professionals almost never use it. So what we're going to do is try connecting using the machine's IP address.

If you don't know what it is, open up a command prompt and just type in "ping" and then the name of the machine, Kirk. In my case, it's Kirk. So there's the IP address: 192.168.2.108. If you're not getting any replies, you're not connected right. Go watch my file sharing video. You should at least be able to ping that machine.

Another way you can find the IP address is on the machine. I'm back on that Kirk machine now. Command prompt here and type in "ipconfig" - that will show you your IP address. It'll show you different network adapters and stuff, but here's your IPv4 address right there. Same thing.

So again, we're going to connect and we're going to type in the IP address of Kirk: 192.168.2.108. We're going to use SQL Authentication. I'm going to put my username in there, I'm going to put my password in there - 123123. I'm going to hit Connect.

Now, if it's taking this long, it's not going to connect. I just know from experience. There's one more thing we're going to check. Let's see here. Give it a second. Yes, see, "Could not open a connection to SQL Server." There's one more thing we're going to check, and this is on a lot of machines. I check this one last because sometimes you don't need this, but sometimes you do - it's your Windows Firewall settings.

So we're going to go back over to Kirk now. I'm back on Kirk. We're going to open up Windows Defender. Start, type in D-E-F-E, and right here, Windows Defender Firewall with Advanced Security. This guy. It's got firewall rules. For those of you who don't know what a firewall is, it's basically a piece of software that says, "Oh, you're trying to connect to this computer? No, you can't. I'm protecting it, you're locked down." And your machine is trying to connect to SQL Server through a particular port, but Defender is saying, "No, this port's closed."

So we basically have to open up a port, an inbound port, in your firewall. First, check to see if you have one. We're going to use a TCP port, so you can scroll down here and see if you have any TCP rules. You probably don't, but if you do, you might have something in here that even is conflicting. But if you haven't played with this at all, chances are you don't have one.

We're going to create a new inbound rule. An inbound rule says we're going to let some traffic get in on a particular port that SQL Server needs. Right click on Inbound Rules, go to New Rule. We're going to go "Port." That's a rule that controls connections for a particular port. Go to Next. We're going to use TCP. I'm not going to go over the difference between TCP and UDP, that's a different class. Specific ports: the port that SQL Server uses is 1433, usually. Type in 1433. Next. "Allow the connection." Next. You want Domain and Private. You can turn off Public if you want to, so if you connect to a public network, it doesn't, but you're not going to be connected to a public network. Don't worry about that. Just these two are what you need. Hit Next. Type in the name. We'll call this "TCP for SQL Server 1433," whatever you want to call it. This is just your friendly neighborhood name. And then hit Finish. Now you have a TCP port open in your firewall, and this should be the last thing you have to do.

Close it. These are all the little things that Windows has configured that are working against you for setting up SQL Server. Trust me, I pulled my hair out many, many times the first time I set this up myself.

Now I'm going to go back to SQL or SSMS on my workstation. I'm not on Kirk anymore. Now we're going to try and connect one more time. Connect, Database Engine. This time, I'm going to try browsing again. Let's try browsing. Let's see if it comes up here. Sometimes it does. Sometimes after you set up that firewall port, you can browse the network and it'll show up. Still not showing up on a browse. That's not a big deal.

Again, try the server name again if you want, or just type in the IP address. I pretty much almost always use the IP address: 192.168.2.108. By the way, I don't think I mentioned this in my file sharing video, but if you're going to be using SQL Server, you definitely want to set up a static IP address for that machine. It's easier for everybody to connect to it then.

Again, Rick and 123123. Now let's see if we can get through the firewall. I can tell it connected immediately. You see, as soon as you open up that firewall port, you're in. Here's your databases. There's that, and here are your tables, and we're in the dbo.Customer table.

I'm just curious, let's see if we can get in there. I'm going to disconnect - File, Disconnect. I'm going to try connecting by just using "Kirk." Let's see if we can connect with just Kirk. Once you get in through the firewall, it should connect. Yes, there we go. You could ping Kirk. It should be able to resolve the IP address and get you in that way, and that way you can use the name on your network instead of having to have a static IP address. But it's still a good idea.

Now, what about your Windows-authenticated users? If you've already got your network set up and you've already got permissions and user accounts and all that set up, can they connect through Windows authentication? Yes, absolutely. Let's disconnect again. Let's go back over to Kirk and make sure the login account is set up properly on this machine. Remember, to use Windows Authentication, you have to have the account set up on Kirk for your users, like I show in my file sharing video. That's under Settings, then Accounts, and then come down to Other Users right here. We'll add an account. I already set up a couple of other users in different videos. We'll add a new account here. I don't have this person's sign-in information. I want to add a user without a Microsoft account. Microsoft is going to try to get you to sign up for all this stuff. Who's going to use this PC? Put any username in here. Let's call this SQL Database User. Again, I'll make their password 123123. In case you forget your password, do all this stuff. Obviously, you will put the proper answers in here and then hit Next.

Now we have an SQL Database User. You can set up a separate user for each of your people or you can have one account for all of them to use; it's completely up to you.

Now, still on Kirk, we're going to go back into SSMS. Login as yourself. Security, Logins. We're going to right click, New Login. Now, since we're going to be using Windows Authentication, then it's a person on this machine. Hit Search. Here, type in that SQL Database User and then hit Check Names. There it is. Hit OK. That's the proper name right there. It's KIRK\\SQL Database User. Map their permissions, pick the database. They're going to be a data reader, data writer. That means they can pretty much do everything data related. Hit OK. Now they're set up. Here they are.

Again, we're on Kirk right now. So I'm going to close this and go back to my workstation. Actually, I'm going to hop over onto a different machine. This is my SPOCK machine. Yes, I have Kirk, Spock, Picard, leave me alone.

I logged on, I created a user account on SPOCK called SQL Database User. This is to show you that if they match - if the usernames and passwords match on both machines - then Windows Authentication should let us in. So this is the same thing: if you have another user whose PC logon is Joe, just make sure you set them up a Joe account on the server with the same password.

I'm going to load up SSMS. Now, this is an older version of SSMS. I haven't updated it. So it's version 18, a little older, but it still works, still does the same thing.

The window comes up. I already typed in the IP address. Let me zoom in a little bit here better. So there's the IP address of the server. Notice it's going to connect from SPOCK as SQL Database User, but we're going to use Windows Authentication. So as long as there's an SQL Database User on Kirk, which is the server, it should connect. I can tell already it's in. There we go. See? Now we're in Kirk. Databases, there it is, and there's my stuff. There are my tables, and there we go. There's my table.

Now, the reason why I did a logon from my workstation is because on my workstation, I log on with a Microsoft account. So here's the deal: if you're signed into Windows using an email address, that's a Microsoft account, and those don't always authenticate clearly across peer-to-peer networks with SQL Server. So rather than fighting Windows, in that case, it's usually easier to create matching local accounts or use SQL Server Authentication. I would suggest the latter.

If you want to keep everybody logged in with their Microsoft accounts, which is how Microsoft wants it, then I would set up SQL Server Authentication for all of your users. It's just going to be easier to deal with. If you already have local Windows authentication accounts set up on all those machines, use that, but SQL Server authentication will work in either case.

So let's pause for a second because I know that was a lot of moving pieces all at once. Here's the quick recap of everything we just did:

First, we made sure SQL Server itself was running on the server machine. Sounds obvious, but if the engine isn't running, nothing is going to connect to it.

We opened the SQL Server Configuration Manager and enabled the TCP/IP protocol. That's what allows SQL Server to accept connections from other machines on the network.

After that, we started the SQL Server Browser Service, so named instances like SQL Express can be discovered automatically when you browse the network. It doesn't always work, but sometimes it does.

We verified that the Windows Network Profile on the server was set to private, not public, so discovery traffic isn't blocked.

From there, we enabled mixed mode authentication so we can use both Windows logins and SQL Server logins. We created SQL logins and mapped them to our database, giving either full control or read-write data access depending on the user.

Since we still couldn't connect, we had to open inbound TCP port traffic on port 1433 in the Windows Firewall, so remote machines could actually reach the SQL Server engine instead of Defender blocking the connection.

Finally, we tested the connection from a workstation using SSMS to make sure everything was working. If you follow those steps in order, you should now be able to connect to your SQL Server engine from another machine on your network.

If you did follow all these steps that we just covered and you're still having trouble connecting, it might be time to contact your IT person or whoever manages your network, because at that point, the issue is likely outside of SQL Server itself. If that network problem is you, I don't know what to tell you. Feel free to post a comment down below or in the forums on my website and let me know what you're running into. If I start seeing a pattern where a lot of people are hitting the same snag, I can always record a follow up video to cover it or something different, maybe a Windows Networking video or something.

But realistically, what we did here should handle about 95 percent of typical small office installations. Like I said earlier, make sure basic file and print sharing is working between your machines. If you can't share folders or browse the network, SQL Server connectivity is going to fail for the same underlying networking reasons. This is not a networking class, so what we covered here is the bare minimum to get SQL Server up and running on your network.

Today, hopefully you got your workstation connected to SQL Server, walked through the course setup that makes remote access work. If you found this helpful, post a comment down below. Let me know what you liked about today's video and what you'd like to see in future lessons.

That's going to do it for lesson eight. Hope you learned something. Live long and prosper, my friends. I'll see you in lesson nine, and we're finally going to start looking at queries and views.

Quiz

Q1. What is the default network connectivity setting for a new SQL Server Express installation?
A. Remote connections are allowed by default
B. Remote connectivity is disabled for security
C. Only domain users can connect by default
D. SQL Server authentication is required by default

Q2. Why does the video recommend testing file and folder sharing between your machines before attempting SQL Server connections?
A. File sharing proves your machines are connected and permissions are correct
B. SQL Server requires file sharing to function
C. It automatically creates SQL logins for your users
D. File sharing installs SSMS automatically

Q3. If you want to allow remote connections to SQL Server Express, what protocol must be enabled in SQL Server Configuration Manager?
A. HTTP
B. UDP
C. TCP/IP
D. Named Pipes only

Q4. What is the purpose of the SQL Server Browser service?
A. It handles database queries
B. It allows server discovery when browsing network servers
C. It controls user permissions inside databases
D. It provides firewall protection

Q5. Why is it important to set your Windows Network Profile on the SQL Server to Private?
A. Private profile increases security against all attacks
B. Private profile allows discovery and connections from other machines on your network
C. Private profile disables all security features
D. Private profile is required for Windows updates

Q6. What is the recommended TCP port to open in Windows Firewall for SQL Server connections?
A. 80
B. 443
C. 1433
D. 3306

Q7. What should you do if the server does not appear in the SSMS browse network list from your workstation?
A. Give up and reinstall SQL Server
B. Use the IP address of the server to connect directly
C. Only use the server name
D. Switch to using MySQL

Q8. Why might SQL Server authentication be preferable to Windows authentication, especially on peer-to-peer networks or with Microsoft accounts?
A. SQL Authentication is more secure than any Windows authentication
B. SQL Authentication does not rely on Windows account matching and works regardless of domain/local/Microsoft accounts
C. Windows authentication is completely unsupported by SSMS
D. SQL Authentication is required for all databases

Q9. What is the correct order of steps to enable remote access to SQL Server Express?
A. Install SSMS, change user passwords, enable mixed mode
B. Enable TCP/IP, start SQL Server Browser, set network profile to private, enable mixed-mode authentication, create firewall rule, create SQL logins
C. Update Windows, share folders, restart server, test queries
D. Set network profile to public, block port 1433, disable TCP/IP, use only Windows authentication

Q10. When setting up SQL logins for your users, which roles should you assign to a user who needs to both read and update database records, but not change table designs?
A. db_owner
B. db_datareader and db_datawriter
C. db_backupoperator
D. db_securityadmin

Q11. What do you need to do on both the client and server machines to successfully use Windows authentication for SQL Server access in a workgroup setting?
A. Match machine names
B. Use Microsoft logins only
C. Create matching usernames and passwords on both server and client
D. Disable Windows Defender

Q12. Why is it recommended to set a static IP address for your SQL Server machine?
A. It is necessary for activating SQL licensing
B. It ensures the IP address does not change, making it easier for clients to connect
C. It increases overall network speed
D. It improves database backup performance

Q13. What is the function of the db_owner role in SQL Server?
A. It allows just reading data
B. It gives users full control over database design and data manipulation
C. It restricts access to just stored procedures
D. It allows for backups only

Q14. If you have followed all the steps in the video and still cannot connect to SQL Server, what is probably the next best step?
A. Reinstall Windows
B. Contact your IT person or seek help in forums
C. Uninstall SQL Server Browser
D. Reboot all network devices

Q15. What is a key limitation of using Microsoft accounts for Windows authentication on peer-to-peer networks with SQL Server?
A. Microsoft accounts never allow file sharing
B. They may not authenticate clearly across workgroup SQL Server connections
C. They require SQL Server Agent to be enabled
D. They must be set as enterprise administrators

Answers: 1-B; 2-A; 3-C; 4-B; 5-B; 6-C; 7-B; 8-B; 9-B; 10-B; 11-C; 12-B; 13-B; 14-B; 15-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from SQL Server Learning Zone is all about connecting to your SQL Server from a different machine on your network. This is something many people have been asking for, since up to this point we've only been working directly on the server itself. In a real-world setup, you and your users are going to need to access SQL Server remotely across the network, not by sitting at the server.

In this lesson, I'll guide you through the process of setting up remote access to your SQL Server. We'll start by showing the basic steps for connecting from another computer, then cover several troubleshooting steps, because in most setups things don't always work on the first try. If you get connected without any issues, you can stop there, but if you do run into problems, keep watching, as we'll go through all the common pitfalls and how to resolve them. Don't be concerned by the length of this session - you may not need the entire lesson if your connection works up front.

Originally, I intended to move on to SQL queries at this stage, but I've received a lot of feedback that learning to connect from another computer is a more immediate need. Since most actual work is done from a separate machine rather than the server itself, that's what we'll focus on today.

So far, everything we've done has been with the server. We've set up our database, built tables, and entered sample data. But in practice, users won't be sitting at the server. It's important to understand the steps needed to get connected remotely.

For those familiar with Microsoft Access and split databases, if you've been sharing your back-end file from this machine, you're already halfway to a network setup. If you've previously set up shared folders and workstations are able to browse these, you're in a good position - it proves your machines can authenticate, browse, and communicate.

If you haven't done this yet, you should, as this is an important foundation. I recently recorded a video showing how to share folders and set up Windows user accounts so your machines can talk to each other. Although file sharing is not required for SQL Server, it's a quick way to check your network permissions and authentication between machines. Think of it as a good connectivity test before tackling SQL Server configurations.

By default, SQL Server Express is locked down for security and only accepts local connections. This is a frequent point of confusion - many people find they can't connect remotely, and this is why.

Make sure first that SQL Server itself is running and that you can connect to it locally using SQL Server Management Studio (SSMS). As basic as it sounds, one of the most common issues is that the SQL Server service isn't running.

The next step is to open the SQL Server Configuration Manager and check that the TCP/IP protocol is enabled. On your server, open the Configuration Manager for your SQL Server version, expand the network configuration, and select the protocols for your instance. TCP/IP is typically disabled out of the box; you'll need to enable it. Once you do so, restart the SQL Server service for the change to take effect.

Without TCP/IP enabled, SQL Server will only accept connections from the local machine, blocking all remote network connections.

After that, turn on the SQL Server Browser Service. This service allows your server to be discoverable when you're trying to browse for it from another computer. In more secure environments, you might want to leave this disabled, but most small office situations will benefit from this being on. Set its startup type to automatic so it starts with SQL Server, then start the service.

At this point, it's a good idea to check your Windows Network Profile to ensure your server is set to use a private network rather than a public one. This affects network discovery and permissions. Settings for this are found under Network and Internet properties in Windows. Choose private network for your wired Ethernet connection, as public is reserved for untrusted networks.

I also want to touch on authentication modes. Earlier, I said we'd use Windows Authentication, but I'll now show you how to enable SQL Server Authentication as well. If you have a peer-to-peer network and have shared Access back-end databases before, Windows Authentication should work fine - you just need matching Windows accounts on both machines. However, not everyone will have this environment. If you want a consistent method that works the same regardless of your Windows setup, SQL Server Authentication uses credentials managed entirely inside SQL Server. This is also what most web SQL hosts use.

To enable SQL Server logins, you need to turn on Mixed Mode Authentication. In SSMS, right click on your server, go to Properties, and under the Security tab, select "SQL Server and Windows Authentication mode." Apply the change, then restart SQL Server for it to take effect.

Now, you can create SQL logins. Under the Security section in SSMS, right click Logins and add a new login using SQL Server Authentication, set a password, and adjust account settings. For administrators or power users, map the account to your database and assign the db_owner permission. For ordinary data users, grant data reader and data writer roles, which allows record editing without giving access to alter the structure of the database.

For setups needing only read access, you can create accounts with only the data reader role. This is one area where SQL Server offers much more fine-grained control than Access.

After setting up server authentication, move to your client workstation. You'll want to connect using SSMS from this other computer. Often, when you try to browse for the SQL Server from the network, nothing will appear - this is normal, especially in small business environments, because the SSMS browse feature isn't reliable. Instead, connect directly using the server's IP address. You can find this IP by pinging the server's host name or using the ipconfig command on the server itself.

Once you know the server IP, attempt to connect with the SQL login credentials. If you run into a "Could not open a connection" error, there's one more commonly overlooked step: configuring Windows Firewall on the server to allow traffic through TCP port 1433, which is the default port for SQL Server. Open Windows Defender Firewall with Advanced Security on the server, add a new inbound rule for TCP port 1433, allow the connection for domain and private networks, and name your rule accordingly. This should be the last tweak needed for most situations.

After that, head back to your workstation and try connecting again using the server's IP and the SQL login. If Windows Firewall was the issue, the connection should now succeed immediately. Remember that using a static IP address for your server is recommended, as it makes connecting from other machines much easier and more consistent.

If you prefer to use Windows Authentication, and you already have your user accounts set up and matched between the workstation and the server, simply add those accounts under Security in SSMS as Windows logins, map their permissions to your database, and use Windows Authentication when connecting from the client. To make Windows Authentication work in peer-to-peer Windows environments, the account names and passwords need to match on both the client and the server.

On new versions of Windows, users often sign in with a Microsoft account. These can be inconsistent for authentication with SQL Server in peer-to-peer setups, so if that's the case, it's usually simpler to use SQL Server Authentication for everyone.

To recap, the process is as follows:
- Confirm SQL Server is installed and running on the server.
- Use Configuration Manager to enable TCP/IP protocol.
- Enable and start the SQL Server Browser Service.
- Make sure your network is set to "private" in Windows.
- Switch to Mixed Mode Authentication so you can use both Windows and SQL Authentication.
- Create the needed user logins and map database permissions.
- Open TCP port 1433 on Windows Firewall to allow remote connections.
- Test connectivity from another workstation, ideally using the server's IP address and your SQL credential.

If you've followed all these steps and are still unable to connect, it may be time to consult your IT department or whoever handles your network, as the underlying issue could be outside SQL Server itself. If you're the one managing things, and you're still stuck, you can always post your questions in the website forums. If enough people encounter the same snag, I'll consider creating more materials to address those networking issues.

What we've covered today will cover about 95 percent of small office setups. Again, make sure basic networking and file sharing function properly between machines. SQL Server needs the same core connectivity. This isn't a full networking course, just the essentials you need to get SQL Server available across your network.

By now, you should be able to make a remote connection to your SQL Server from another machine. I hope you found this lesson helpful. If you did, let me know in the comments and share what you'd like covered next.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Connecting to SQL Server from another machine on the network
Testing network connectivity with file sharing
Enabling TCP IP protocol in SQL Server Configuration Manager
Restarting SQL Server services to apply network changes
Starting and configuring the SQL Server Browser service
Setting the Windows network profile to Private
Enabling mixed mode authentication in SQL Server
Creating SQL Server Authentication logins
Mapping SQL logins to specific database roles
Assigning db_owner, datareader, and datawriter roles
Finding the SQL Server machine's IP address
Connecting to SQL Server remotely using SSMS and IP address
Opening TCP port 1433 in Windows Firewall for SQL Server
Setting up matching Windows user accounts for authentication
Creating Windows Authentication logins in SQL Server
Assigning database roles to Windows-authenticated users
Understanding when to use SQL Server Authentication over Windows Authentication
Testing connectivity from a remote workstation using SSMS

Article

Connecting to SQL Server from another machine on your network is one of the most important steps you'll take after installing and setting up your server. While up until now you've probably been working directly on the SQL Server machine, most real-world use involves connecting to the server remotely so you can manage it conveniently from your workstation or allow other users to access the data.

To get started, it's a good idea to confirm basic network connectivity between your machines. If you already share an Access database backend on your server and your workstation can see shared folders, open files, or even just ping the server by name or IP address, your machines are probably ready for SQL Server networking too. If not, it's wise to test file sharing between computers first to make sure networking and permissions work as expected.

By default, SQL Server Express is locked down for security and only allows local connections. That's why you might find you can't connect from another computer right after installation. One of the first things to check is that the SQL Server service itself is running. You should be able to open SQL Server Management Studio (SSMS) on your server and connect with Windows Authentication.

The next step is to enable the TCP/IP protocol, as this is what allows remote connections. On your server, click the Windows Start button and search for Configuration Manager. Open the SQL Server Configuration Manager (the one matching your SQL Server version, not the 32-bit version). Find SQL Server Network Configuration, expand it, then click on Protocols for your SQL Server instance. By default, TCP/IP will show as "Disabled." Right-click TCP/IP and choose "Enable." Remember that changes here require a restart of the SQL Server service to take effect, so right-click on the SQL Server (SQLEXPRESS) service and choose Restart.

You should also enable the SQL Server Browser service, especially for named instances such as SQL Express. In Configuration Manager, click SQL Server Services. Look for SQL Server Browser - if it's "Stopped," right-click, go to Properties, set Start Mode to "Automatic," click OK, then right-click again and choose "Start." This service lets users browse for the server on the network, which can help some connection tools find it, though browsing doesn't always succeed on every network.

Next, make sure your Windows Network Profile is set to "Private" and not "Public" on the server. Go to Start, then Settings, then Network & Internet. Check your network adapter's properties and verify it's marked as Private. This is important because Windows treats public networks as untrusted and will block many connection types by default.

Now, let's talk authentication. If your network already handles Windows Authentication well, such as in an office where every user has a Windows account and password on the server, you can continue using Windows logins. But to make connecting simpler across a variety of setups, SQL Server can also manage its own logins. This is known as "SQL Server Authentication," and to use it, you'll need to enable "mixed mode" on your SQL Server instance.

To enable mixed mode authentication, open SSMS on the server, right-click the server name at the top of Object Explorer, select Properties, and go to the Security tab. Change the server authentication setting from "Windows Authentication mode" to "SQL Server and Windows Authentication mode." Click OK. This setting also requires a restart of SQL Server from Configuration Manager.

Now you're ready to create logins. In SSMS, expand the Security folder, then Logins. Right-click Logins and select "New Login." For a SQL Server Authentication login, enter a login name, choose "SQL Server Authentication," set a password, and disable options like "User must change password at next login" for simplicity in a small office. You should map each login to the database you want them to access and assign appropriate roles. Use "db_owner" for full control, or "db_datareader" and "db_datawriter" for users who just need to read and modify data, similar to typical Access database users.

Once your remote users are set up, it's time to connect from a workstation. Install SSMS on the remote PC, then try to browse for the server. If it doesn't show up in the list when you click Browse Network, don't worry - browsing is unreliable on many small networks. Instead, find the server's IP address. You can ping the server by name from a command prompt, or from a prompt on the server, use the "ipconfig" command to read its IPv4 address.

Now, try connecting to the server from the remote workstation using its IP address (for example, 192.168.2.108), selecting SQL Server Authentication and entering the username and password you created. If the connection fails, and you get an error like "Could not open a connection to SQL Server," the most common remaining issue is firewall configuration.

By default, Windows Defender Firewall will block incoming connections to the SQL Server port. You'll need to create an inbound rule for TCP port 1433, which is the standard for SQL Server. On the server, open Windows Defender Firewall with Advanced Security (search for "firewall" in the Start menu). Click Inbound Rules on the left, then "New Rule" on the right. Choose "Port," "TCP," and enter "1433" as the specific port. Allow the connection, and apply the rule to Domain and Private profiles (not Public). Give the rule a name you'll recognize, like "SQL Server TCP 1433," then finish the wizard.

After the firewall rule is enabled, try connecting from your workstation again using SSMS, the server's IP address, and your SQL Server login. You should connect almost instantly if all settings are correct. Optionally, try connecting by the server's network name to see if name resolution works.

If your users need to log in using Windows Authentication, make sure matching Windows user accounts exist both on the server and their own workstation, with the same username and password. In SSMS, create a new login using Windows Authentication by searching for the username in the Login dialog. Assign that login rights to your database as datareader/datawriter or as needed.

Remember, if your workstations use Microsoft account sign-ins (email addresses), Windows Authentication between peers on a workgroup network can be problematic. In these cases, SQL Server Authentication for each user is usually simpler and more consistent.

To recap: Confirm the SQL Server service is running; enable TCP/IP in SQL Server Configuration Manager; start the SQL Server Browser service; verify the network profile is set to "Private"; enable mixed mode authentication; create SQL logins and map them to your database with appropriate privileges; and finally, create a firewall rule on port 1433. Once all those steps are complete and your networking is fundamentally functional, you should be able to connect to SQL Server from your networked workstation using either Windows or SQL Server Authentication.

If after following these instructions you still cannot connect, there may be other advanced networking or permission issues in your environment. At that point, it can help to ask an IT professional for help or to check community forums for troubleshooting tips specific to your network setup. However, in the vast majority of small-office deployments, stepping through these settings solves remote connectivity problems with SQL Server Express.

With your remote access set up, you're now ready to move forward and start using your SQL Server from any computer in the office. The ability to manage your data remotely is a big step, and now you can focus on creating, querying, and sharing your database more efficiently.

Navigation

Links

Keywords