Setting up shared azure sql connection for my team

[peanut89]

We have a azure sql db. due to governance the team cannot have direct sql uid and password. And i am trying to set up the connection as admin and share with them. IN DATA CONNECTION in admin panel in alteryx server. 

The thing is when i try to set up a azure sql db, i get the same error. 

Error SQLDriverConnect: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The server principal "DeleMEAlX" is not able to access the database "master" under the current security context.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'DeleteMEAlX'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open user default database. Login failed.

any ideas

[jdminton]

The error message indicates you're trying to connect to the "master" DB. You should have a named DB that you are connecting as all attempts to connect to the master will be blocked for security reasons. I think you likely had to set up an ODBC connection. If so, on the 3rd setup screen you'll see the default database. Go to ODBC Data Sources and click on your SQL connection and then configure. The first couple of pages are likely fine, but at some point you should see one like the second image. This is where you set the default database.

[peanut89]

its specified in my odbc connection and i specify the db in the bottom there. So i dont know why it trying to connect to master

[jdminton]

I'm curious if you've ever been able to log in to the database with this user. Since it mentions "cannot open user default database", that may mean that the security settings in SQL server are pointing you to the master database. You might need to work with an SQL admin to check that/get it changed.

The only other thing I can think of that might be an issue is if the database name isn't correct for some reason (i.e. typo, misread letter like zero and "O", or space characters that are not allowed.)

[peanut89]

When i use ssms . i have to specify the db. and it works. i can log in.

[jdminton]

it's been a while since I set up a connection, so I had to go set up a new one to test. After you enter the credentials and click test, the database names should self populate. Is that happening for you. Also, your screen appears different than mine. I looked in 2022 and 2023. What version of Designer are you running?

[peanut89]

2022.1. When i hit test its not self populating. I have to specify in ssms. in alteryx it just pops up with the error. 

[jdminton]

Are you running Designer Cloud by chance? I don't have that, but coming from cloud, SQL Server may be protected against external connections, which cloud may appear to be. Have you tried other ways to connect (i.e. input tool)?