SQL Server Login

[cubdh23]

Hi, I have an access front end and SQL Server Backend.
I have created a form for users to enter username and password to get gain entry to the Access Front End. My problem is that the SQL Server Login then requires a UserName and Password.

I want to be able to automatically fill in the SQL Server Username and Password from the VBA code in the Access Login form.

So basically if the user enters the right username and password then the program should automatically enter in the SQL Login and SQL Password.

I have no idea how to enter the SQL Login and Password from the code.
Anyway know the syntax for this???

 

[Access Hero]

Is there a reason you wouldn't use Windows authentication for this? Then the user wouldn't have to enter a SQL Server login and the DBA can control individual security if needed.

If not, try reading Banana's excellent ODBC article on this site.

 

[cubdh23]

I would not use Windows Authentication for this.
I would have to install SQL Server on all the machines that will use this database and we do not want to do that.

We just want to link the access to the server with the odbc connection.

 

[Access Hero]

Hmmmm. Is this on a company network using Windows?

If so:
On the second screen of your ODBC setup, you can click on the "With Windows NT authentication using the network login ID.", which will not require you to install anything but the ODBC driver on all the machines.

Then in SQL Server (Enterprise Manager or Management Studio), you can set permissions as appropriate for your objects or database for user "Public". It really requires no code and doesn't require you to install anything you haven't already installed.

If not, take a look at Banana's writeup.

 

[cubdh23]

Thank you.
Yes this is on a company network using windows.
I am not an IT or IS person so sorry for my ignorance

I will follow your advice.

Thanks

 

[SOS]

I would not use Windows Authentication for this.
I would have to install SQL Server on all the machines that will use this database and we do not want to do that.

That is a completely erroneous statement. You do not install SQL Server on all of the machines in order to use Windows Authentication. Windows Authentication means that a user has logged into the domain using their domain login and password and therefore, since they have logged in as themselves, SQL Server will let them through because Active Directory has authenticated them as a valid user.

 

[Access Hero]

Thank you.
Yes this is on a company network using windows.
I am not an IT or IS person so sorry for my ignorance

I will follow your advice.

Thanks

I fear regardless of what you do, you'll need IT buy-in. They'll need to give you a user ID, probably one for each user (so they can control security as employees are hired/fired). Your SQL DBA will also need to set up security on the database or its objects.

 

[cubdh23]

Yes I know I need IT buy-in.
Your mention of our IT will bring up an entire new can of worms.
I told them that i am done and i need help with the security part. They stall and stall and stall as they always do.
It is so so frustrating working with our IT department. Whenever i request something it seems like i need to beg before i get it. They basically set up my database account on one of the servers and basically said " here it is"
I wasted a couple weeks trying to figure out how to use everything.
I find it a lot easier and less upsetting to take matters into my own hands.

Anyway I can go on and on about our IT and my heart rate is increasing again so i will stop here.

Thanks again.

 

[cubdh23]

Yes I know I need IT buy-in.
Your mention of our IT will bring up an entire new can of worms.
I told them that i am done and i need help with the security part. They stall and stall and stall as they always do.
It is so so frustrating working with our IT department. Whenever i request something it seems like i need to beg before i get it. They basically set up my database account on one of the servers and basically said " here it is"
I wasted a couple weeks trying to figure out how to use everything.
I find it a lot easier and less upsetting to take matters into my own hands.

Anyway I can go on and on about our IT and my heart rate is increasing again so i will stop here.

Thanks again.

 

[Access Hero]

Yes, I can understand that IT departments can be frustrating to work with. But unless you control the back-end as well as the front-end, you gotta play their games.

Sorry it's so frustrating for you. Let us know if there's anything we can do to help.

 

[cubdh23]

IS is telling me that i need to set up a DSN-less connection.
I have been connecting using DSN.
I have surfed the net in search for DSN-less connection and it seems to be involved.

Is this something simple to do? Or is it involved?
Because if it is i will put this on hold, I have too many other things to do.

Also does anyone know of any good resource to learn about DSN-less connections

Thanks

 

[SOS]

Also does anyone know of any good resource to learn about DSN-less connections

Read Banana's ODBC post here.

 

[cubdh23]

Great article by Banana. Thanks