Steven Black
asked on
Trouble accessing SQL and Access Database
Good evening,
We are having an issue with our SQL Server Express 2008R2 installation on our new server. We currently have a server running SBS2011, along with SQL Server Express 2008R2. Since support for SBS2011 is ending soon, we have purchased a new server, which currently has Server 2016 Standard and SQL Server Express 2008R2 installed. I am currently testing the new server for functionality in a test environment, and we are unfortunately having an issue.
We currently utilize an Access Database to track all of our patient information, along with SQL Server for authentication and reporting of the Access Database. In order to move the database to the new server, I have backed up the database on the SBS2011 server. I then installed SQL Server on new server (2016 Standard), and then restored the database. When opening the Access Database file, it should query the SQL Server for permissions via Active Directory and SQL Server. I have the authentication on the database set as mixed mode. I have verified the credentials exist in SQL. I am able to connect successfully from a workstation using the Microsoft Kerberos Configuration Manager for SQL Server tool as well.
I have attached screenshots of the errors that I am receiving and would greatly appreciate any input to assist in resolving this issue.
Thanks for the help.
We are having an issue with our SQL Server Express 2008R2 installation on our new server. We currently have a server running SBS2011, along with SQL Server Express 2008R2. Since support for SBS2011 is ending soon, we have purchased a new server, which currently has Server 2016 Standard and SQL Server Express 2008R2 installed. I am currently testing the new server for functionality in a test environment, and we are unfortunately having an issue.
We currently utilize an Access Database to track all of our patient information, along with SQL Server for authentication and reporting of the Access Database. In order to move the database to the new server, I have backed up the database on the SBS2011 server. I then installed SQL Server on new server (2016 Standard), and then restored the database. When opening the Access Database file, it should query the SQL Server for permissions via Active Directory and SQL Server. I have the authentication on the database set as mixed mode. I have verified the credentials exist in SQL. I am able to connect successfully from a workstation using the Microsoft Kerberos Configuration Manager for SQL Server tool as well.
I have attached screenshots of the errors that I am receiving and would greatly appreciate any input to assist in resolving this issue.
Thanks for the help.
ASKER
I have checked the credentials in SSMS and they in fact do exist. I am aware that the logins did not transfer from the old server to the new server, since this is on a new domain. I have manually created a few logins and still am not able to authenticate to the server via the Access Database. I can sign in to SSMS via Windows Authentication.
I suggest to not using Windows Authentication unless you're connecting to database in a secured environment.
For the password part of user "opi", you got to login to SSMS with Administrator first, like (SA), and then set a password for User ID "opi". And then try login in Access again.
For the password part of user "opi", you got to login to SSMS with Administrator first, like (SA), and then set a password for User ID "opi". And then try login in Access again.
I always use simple as well for database backend access as well.
I suppose that windows auth can be good, if he is pulling the information to use for tracking users and it requires it or something.
but I always used different SQL credentials to log in each different user and track activity.
Either way if it is the windows password or the simple password that is app specific, it comes down to a username and password.
I suppose that windows auth can be good, if he is pulling the information to use for tracking users and it requires it or something.
but I always used different SQL credentials to log in each different user and track activity.
Either way if it is the windows password or the simple password that is app specific, it comes down to a username and password.
Regarding his issue I'm guessing that he has one of the following issues:
Credential issues
Firewall issues
SQL browser agent not running.
Credential issues
Firewall issues
SQL browser agent not running.
Doesn't the latest version of MSSQL offer full features for the express version? and only if you exceed a certain size do you have to pay for a license? If he is using express anyways I'd move to current version.
Hello
Check the following:
Check the source SQL server agent logon service account name
All programs --> SQL server 2008 --> Configuration Tools --> SQL server configuration manager. Go to the SQL server agent --> properties --> Check the logon tab and find the account name.
Check the same is available in the target SQL server.
Under the security tab -->logins. If it’s not available add the domain user with necessary permission on your target machine.
Check the following:
Check the source SQL server agent logon service account name
All programs --> SQL server 2008 --> Configuration Tools --> SQL server configuration manager. Go to the SQL server agent --> properties --> Check the logon tab and find the account name.
Check the same is available in the target SQL server.
Under the security tab -->logins. If it’s not available add the domain user with necessary permission on your target machine.
ASKER
Thanks everyone for the assistance. I will look further into this today and let you know what I find out.
ASKER
So I have been working on this, and I am still in the same position. I have verified all settings for SQL between the old server and the new server in both SQL Configuration Manager as well as SSMS. For a test, I have created a new user in SSMS with SQL Server Authentication. Once that new user is created, I opened SSMS and logged in to it using those credentials without issue. I am able to log in to SSMS with both Windows authentication as well as newly created SQL credentials.
To eliminate firewall and an untrusted domain issue, I have also attempted to open the .mdb file on the server itself. Once the file is opened, I am prompted to provide credentials. No matter the credentials that I provide, whether they are the Windows or SQL credentials, I still get the same errors as stated above.
I feel like I am missing something simple, that I am just not noticing. Any ideas?
To eliminate firewall and an untrusted domain issue, I have also attempted to open the .mdb file on the server itself. Once the file is opened, I am prompted to provide credentials. No matter the credentials that I provide, whether they are the Windows or SQL credentials, I still get the same errors as stated above.
I feel like I am missing something simple, that I am just not noticing. Any ideas?
Are you trying to login with SQL Server Authentication or Windows Authentication?
If you are logged in to Windows as ROSEWOOD3\OPI and that is an existing Login on SQL Server using Windows Authentication then you should never ever enter a user name and password.
If you are logged in to Windows as ROSEWOOD3\OPI and that is an existing Login on SQL Server using Windows Authentication then you should never ever enter a user name and password.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
try go to MS SQL Server via SSMS and check what's the correct credentials. You need to enter the correct Login ID and Password.