MS Access to SQL Server Express 2008
I have a database that is on a PC. This PC is a server and is the host for the SQL Express instance. I can connect to the SQL Server if I use MS Access and create and ODBC connection manually. I cannot connect to is using VBA coding. I am pretty sure the coding is correct as it is working for two other databases on other networks right now. What I am not sure of is the Security features of SQL Server. I think I covered them all. The user is included in the instance and has been assigned as SYSADMIN. I Configure SQL Server to accept TCP/IP connections. I am at a loss.
Ok well it looks like when you are using a trusted connection the login works but you are denied access to your database which means you need to set up that login user with rights to the database you want to use on SQL Server. On the SQL Server management console if you go under the database and open the security folder you should be able to add the user to a role there and or assign them additional rights.
To expand on what kmslogic said:
SQL Server has two "authentication" methods - Windows Authentication and SQL Authentication. Note you can use BOTH authentication methods at the same time, which is known as "mixed mode".
Windows Authentication (i.e. "Use Trusted Connections") basically means that if you can log into Windows, then you can log into the SQL Server.
SQL Authentication means that you must enter a username and password that has been defined on the server.
Here's a nice article about the differences in the methods, and when to use a particular mode: http://www.petri.co.il/how-to-choose-sql-server-authentication-mode.htm
However, logging onto the server is only part of the security scheme. Once you do, your ability to interact with the databases on that server is defined by the permissions given to your login or group. These permissions must be setup by an Administrator, in most cases, and if you find that you can login to the server, but cannot connect to a specific database, you must ask your Admin to fix these issues, or do it yourself. kmslogic has given you the basics regarding this, but if you want more information check the links below:
Here's a discussion on the different Permission Types (like SELECT, UPDATE, EXECUTE, etc), and what they allow a user to do. This is for SQL 2000 but are still relevant:
http://www.databasejournal.com/features/mssql/article.php/2246271/Managing-Users-Permissions-on-SQL-Server.htm
You can set permissions using standad SQL statements, or through the SQL Server Management Studio interface. There are thousands of sites that discuss all of the different SQL statements, but you can get much of it straight from MSDN or Book Online (BOL).
MSDN: http://msdn.microsoft.com/en-us/library/ff848791.aspx (you're concerned mostly with the GRANT link)
SSMS - Managing SQL Server Permissions (part 1) - Using SSMS to Manage Permissions at the Server Level:
http://mscerts.programming4.us/sql_server/managing%20sql%20server%20permissions%20%28part%201%29%20-%20using%20ssms%20to%20manage%20permissions%20at%20the%20server%20level.aspx
There are 4 parts to the series above, so check the links at the bottom of the first part to move through the others.
SQL Server has two "authentication" methods - Windows Authentication and SQL Authentication. Note you can use BOTH authentication methods at the same time, which is known as "mixed mode".
Windows Authentication (i.e. "Use Trusted Connections") basically means that if you can log into Windows, then you can log into the SQL Server.
SQL Authentication means that you must enter a username and password that has been defined on the server.
Here's a nice article about the differences in the methods, and when to use a particular mode: http://www.petri.co.il/how-to-choose-sql-server-authentication-mode.htm
However, logging onto the server is only part of the security scheme. Once you do, your ability to interact with the databases on that server is defined by the permissions given to your login or group. These permissions must be setup by an Administrator, in most cases, and if you find that you can login to the server, but cannot connect to a specific database, you must ask your Admin to fix these issues, or do it yourself. kmslogic has given you the basics regarding this, but if you want more information check the links below:
Here's a discussion on the different Permission Types (like SELECT, UPDATE, EXECUTE, etc), and what they allow a user to do. This is for SQL 2000 but are still relevant:
http://www.databasejournal.com/features/mssql/article.php/2246271/Managing-Users-Permissions-on-SQL-Server.htm
You can set permissions using standad SQL statements, or through the SQL Server Management Studio interface. There are thousands of sites that discuss all of the different SQL statements, but you can get much of it straight from MSDN or Book Online (BOL).
MSDN: http://msdn.microsoft.com/en-us/library/ff848791.aspx (you're concerned mostly with the GRANT link)
SSMS - Managing SQL Server Permissions (part 1) - Using SSMS to Manage Permissions at the Server Level:
http://mscerts.programming4.us/sql_server/managing%20sql%20server%20permissions%20%28part%201%29%20-%20using%20ssms%20to%20manage%20permissions%20at%20the%20server%20level.aspx
There are 4 parts to the series above, so check the links at the bottom of the first part to move through the others.
Or since this is SQL server express just login as the system administrator on an untrusted connection using the account "sa" which will give you access to everything on your instance. By default this account is unpassworded. Also you get the benefit of knowing any dba or Microsoft employee will hear nails on a chalkboard whenever you do this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Handled dy 3rd party that installed SQL Server. They made error on install.
What were the security setting errors?
Didn't you say SQL Express in your original question? You had a third party install that and they made security mistakes?
ASKER